One Foreign Key with 2 References Table

Question

is it possible to have a foreign key with two reference tables?

here's the script and we're having a problem inserting into this table

CREATE TABLE Class(
    idNumber varchar(30) not null,
    day varchar(10) not null,
    time varchar(20) not null,
    foreign key(idNumber) REFERENCES ThesisMember(idNumber),
    foreign key(idNumber) REFERENCES Faculty(idNumber),
    PRIMARY KEY(idNumber,day,time)
);

CONSTRAINT `class_ibfk_1`
    FOREIGN KEY (`idNumber`) REFERENCES `thesismember` (`idNumber`))

SQL Statement:

INSERT INTO `thesis`.`class`
    (`idNumber`, `day`, `time`) VALUES ('9990', 'F', '0940-1110')

the Faculty table has the "9990" idNumber.

it is not possible. you cannot reference one column with two tables. — John Woo, Mar 06 '13 at 07:55
What if *both* tables have a row with `9990`? Is this a reference to both tables? Or to just one? Which one? How would *we* decide, on looking at it? How, then, would MySQL decide? — Damien_The_Unbeliever, Mar 06 '13 at 07:55
isn't it much easier to find out whether it's possible or not just by trying it out? — Blizzer, Mar 06 '13 at 08:00
@DreamEater how? only one table(Faculty or ThesisMember) can have "9990". — Kopaka, Mar 06 '13 at 08:23

score 1 · Accepted Answer · answered Mar 06 '13 at 08:25

1

The FK constraint requires that every instance of the field 'idNumber' occur in ALL referenced tables. You state that only one of the referenced tables has the value to be inserted, so of course an error occurs on the INSERT attempt.

answered Mar 06 '13 at 08:25

Pieter Geerkens

11,775
2
32
52

No; it is possible PROVIDED that the idNumber is always going to occur in both tables. You should have a master table owning the PK idNumber, with ThesisMember and FacultyMember being detail tables off of it. An additional idType field would identify whether an idOwner was a ThesisMember or a FacutlyMember. – Pieter Geerkens Mar 06 '13 at 08:48

One Foreign Key with 2 References Table

1 Answers1

Linked