0

These are my tables:

CREATE TABLE uregister(
u_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
name VARCHAR(150) NOT NULL,
u_mail VARCHAR(150) NOT NULL,
u_password VARCHAR(8) NOT NULL,
PRIMARY KEY(u_id,u_mail)
);
CREATE TABLE dregister(
d_id GENERATED BY DEFAULT AS IDENTITY NOT NULL,
name VARCHAR(150) NOT NULL,
d_mail VARCHAR(150) NOT NULL,
d_subject VARCHAR(150) NOT NULL,
d_experience INT NOT NULL,
d_occupation VARCHAR(150) NOT NULL,
d_password VARCHAR(8) NOT NULL,
PRIMARY KEY(d_id,d_mail, d_subject)
);
CREATE TABLE question(
q_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
question VARCHAR(500) NOT NULL,
email VARCHAR(150) NOT NULL,
PRIMARY KEY(q_id, email),
FOREIGN KEY(email)  REFERENCES uregister(u_mail),
FOREIGN KEY(email)  REFERENCES dregister(d_mail)
);

Can an email field refer to two different PRIMARY KEY (or UNIQUE) fields at a time? Under this schema, only one user or one domain can ask a given question?

  • Read up on sql table inheritance, aka subtypes/supertypes – Neil McGuigan Mar 2 at 17:11
  • if u_mail, d_mail are declared unique it is not a problem – Lennart Mar 3 at 6:51
2

You can do it in both SQL Server (see fiddle here) and IBM DB2 (fiddle):

I adapted your table create syntax slightly because it fails on SQL Server 2019 on the fiddle otherwise (I imagine it would fail on all versions of SQL Server). Your syntax works on IBM DB2.

The first table:

I had to change (SQL Server) the IDENTITY column slightly from u_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, to u_id INT IDENTITY NOT NULL, otherwise the statement fails.

CREATE TABLE uregister(
u_id INT IDENTITY NOT NULL,
name VARCHAR(150) NOT NULL,
u_mail VARCHAR(150) UNIQUE NOT NULL,
u_password VARCHAR(8) NOT NULL,
PRIMARY KEY(u_id,u_mail)
);

And I had to change (both servers) the line u_mail VARCHAR(150) NOT NULL, to u_mail VARCHAR(150) UNIQUE NOT NULL, otherwise the following error occurs on trying to create the child table. This is the case with all other servers AFAIK - you have to have a child's parent field be either UNIQUE or a PRIMARY KEY (on its own, and not just part of a PK (if it's only part of a PK, it is not guaranteed to be unique in that table!).

Msg 1776 Level 16 State 0 Line 1
There are no primary or candidate keys in the referenced table 'uregister' that match the referencing column list in the foreign key 'FK__question__email__286302EC'.
Msg 1750 Level 16 State 1 Line 1
Could not create constraint or index. See previous errors.

If the PK or UNIQUE constraint(s) are not present for that field in the IBM DB2 fiddle, this is the error:

[IBM][CLI Driver][DB2/LINUXX8664] SQL0573N  A column list specified in the references 
clause of constraint "EMAIL..." does not identify a unique constraint of the parent 
table or nickname "FIDDLE_UHGEYTFVKRUDCQDSXLQL.UREGISTER".  SQLSTATE=42890 SQLCODE=-573

Same thing for the dregister table!

CREATE TABLE dregister(
d_id INT IDENTITY NOT NULL,
name VARCHAR(150) NOT NULL,
d_mail VARCHAR(150) UNIQUE NOT NULL,
d_subject VARCHAR(150) NOT NULL,
d_experience INT NOT NULL,
d_occupation VARCHAR(150) NOT NULL,
d_password VARCHAR(8) NOT NULL,
PRIMARY KEY(d_id,d_mail, d_subject)
);

Then, the two foreign keys are created without problem (as per the fiddle).

CREATE TABLE question(
q_id INT IDENTITY NOT NULL,
question VARCHAR(500) NOT NULL,
email VARCHAR(150) NOT NULL,
PRIMARY KEY(q_id, email),
FOREIGN KEY(email)  REFERENCES uregister(u_mail),
FOREIGN KEY(email)  REFERENCES dregister(d_mail)
);
| improve this answer | |
  • i am using ibm db2 database and the syntax you give is also not run on this database it shows error ...SQL0104N An unexpected token "NOT" was found following "R( u_id INT IDENTITY". Expected tokens may include: "<references_spec> ,when i run the uregister table – Rashmi Kumari Mar 3 at 5:46
  • Well, now I don't understand. You did look at the fiddle here? I don't know why it works there but not with you - but that's the only access I have to an IBM instance. You'll have to figure out the CREATE TABLE on that server yourself - experiement. Maybe, if you have something defined as IDENTITY it can't be NULL anyway under IBM's syntax? Once you get the table created, I think that the issue about creating a dual foreign key won't be a problem! – Vérace Mar 3 at 6:35

Not the answer you're looking for? Browse other questions tagged or ask your own question.