Add constraints for a foreign key that references multipleprimary keys from different tables in SQL Plus?

Question

I'm a beginner learning SQL and am having trouble implementing this concept.

Suppose you create the following three tables:

CREATE TABLE dogOwner(
 ownerNo VARCHAR(8) CONSTRAINT ownerNo_pk1 PRIMARY KEY,
 ownerName VARCHAR(10)
);

CREATE TABLE catOwner(
 ownerNo VARCHAR(8) CONSTRAINT ownerNo_pk2 PRIMARY KEY,
 ownerName VARCHAR(10)
);

CREATE TABLE petsAdopted(
 petNo VARCHAR(8) CONSTRAINT petNo_pk PRIMARY KEY,
 ownerNo VARCHAR(8) CONSTRAINT ownerNo_fk1 REFERENCES dogOwner(ownerNo)
                    CONSTRAINT ownerNo_fk2 REFERENCES catOwner(ownerNo)
);

How do you properly create constraints for the foreign key ownerNo, which references ownerNo from two other tables?

score 5 · Answer 1 · answered Jul 16 '20 at 14:31

You can't. You could have 2 columns in petsAdopted: dogOwnerNo and catOwnerNo and 2 foreign keys. But the table design doesn't seem to make sense: surely a pet either is a dog or a cat (or something else) regardless of who owns it?

Here is an alternative design:

CREATE TABLE owner(
 ownerNo VARCHAR(8) CONSTRAINT ownerNo_pk2 PRIMARY KEY,
 ownerName VARCHAR(10)
);

CREATE TABLE petsAdopted(
 petNo VARCHAR(8) CONSTRAINT petNo_pk PRIMARY KEY,
 petType VARCHAR2(10) NOT NULL CONSTRAINT petTypeChk (CHECK petType in ('CAT','DOG'))
 ownerNo VARCHAR(8) CONSTRAINT ownerNo_fk REFERENCES owner(ownerNo)
);

These are not actual tables I am working with! I'm trying to do an assignment for class, so I didn't want to use the actual tables given in the question. I think I have it figured out now, and your response triggered my understanding.Thanks for taking the time to respond! — phobicghost, Jul 16 '20 at 15:54

JagaSrik · Answer 2 · 2020-07-16T14:55:22.750

0

This only address syntax part of your answer which You have got lot of syntax error, but this will not work, Please rethink your design.

 CREATE TABLE dogOwner(
 ownerNo VARCHAR(8) CONSTRAINT ownerNo_pk1 PRIMARY KEY,
 ownerName VARCHAR(10)
);

CREATE TABLE catOwner(
 ownerNo VARCHAR(8),
 ownerName VARCHAR(10),
 CONSTRAINT ownerNo_pk2 PRIMARY KEY (ownerNo),
 CONSTRAINT ownerNo_fk1  FOREIGN KEY (ownerNo) REFERENCES dogOwner(ownerNo)
);

CREATE TABLE petsAdopted(
 petNo VARCHAR(8) ,
 ownerNo VARCHAR(8),
CONSTRAINT petNo_pk PRIMARY KEY (petNo),
CONSTRAINT ownerNo_fk_pet1 FOREIGN KEY (ownerNo) REFERENCES dogOwner(ownerNo),
CONSTRAINT ownerNo_fk_pet2  FOREIGN KEY (ownerNo) REFERENCES catOwner(ownerNo)
);

edited Jul 16 '20 at 14:55

answered Jul 16 '20 at 14:36

JagaSrik

700
7
23

2

This is not going to work because `ownerno` must reference existing values. Assume you have a dogowner with `ownerno = 'one'` and a catowner with `ownerno = 'two'` then you can insert a row referencing the dogowner in `petsadopted` because it would violate the FK to `catowner` – Jul 16 '20 at 14:46
Got it, what you saying design is wrong, my answer only address on syntax part of it but not address the conceptual part of it , i will edit and mention this in my answer. – JagaSrik Jul 16 '20 at 14:54

Add constraints for a foreign key that references multipleprimary keys from different tables in SQL Plus?

2 Answers2