Model a ISA relation in MySQL with Foreign Keys

database-design, foreign-keys, model, mysql

Solution

You are doing it backwards - a FOREIGN KEY from the "specific" table should reference the "general" table.

In your case: `Docent` should reference `Person` and `Student` should also reference `Person`, similar to this:

CREATE TABLE IF NOT EXISTS Person (
  person_id int(4) unsigned AUTO_INCREMENT,
  -- Other fields...
  PRIMARY KEY (person_id)
);

CREATE TABLE IF NOT EXISTS Student (
  student_id int(4) unsigned,
  -- Other fields...
  PRIMARY KEY (student_id),
  FOREIGN KEY (student_id)
    REFERENCES Person (person_id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS Docent (
  docent_id int(4) unsigned,
  -- Other fields...
  PRIMARY KEY (docent_id),
  FOREIGN KEY (docent_id)
    REFERENCES Person (person_id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

Insert a new student by:

- First inserting a row into `Person` and getting the generated `person_id` (e.g. via LAST_INSERT_ID).

- And than using that same value for `student_id` when inserting a row into `Student`.

Ditto for docent.

Problem

How to model an ISA class? A 'Persoon' ISA 'Student' or a 'Docent', how do you model this in MySQL? I tried to make a Persoon class, with 2 field and then you can put the ID of the student / docent in the correct field. The other one is zero. ``` CREATE TABLE IF NOT EXISTS `Docent` ( `docent_id` int(4) unsigned NOT NULL AUTO_INCREMENT,, PRIMARY KEY (`docent_id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS `Persoon` ( `naam` varchar(50) NOT NULL, `email` varchar(50) NOT NULL, `wachtwoord` varchar(100) NOT NULL, `student_id` int(4) unsigned DEFAULT NULL, `docent_id` int(4) unsigned DEFAULT NULL, PRIMARY KEY (`email`), KEY `email` (`email`), KEY `student_id` (`student_id`), KEY `docent_id` (`docent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS `Student` ( `student_id` int(4) unsigned NOT NULL AUTO_INCREMENT, `studentnummer` int(20) NOT NULL, `niveau` int(2) NOT NULL, `notities` varchar(200) NOT NULL, PRIMARY KEY (`student_id`), UNIQUE KEY `email` (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; ``` When adding the foreign keys, as shown below I get a #1452 error. I looked it up, but did not find how to solve it. ``` ALTER TABLE `Persoon` ADD CONSTRAINT `Persoons_student_id` FOREIGN KEY ( `student_id` ) REFERENCES `software`.`Student` (`student_id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `Persoon` ADD CONSTRAINT `Persoons_docent_id` FOREIGN KEY ( `docent_id` ) REFERENCES `software`.`Docent` (`docent_id`) ON DELETE CASCADE ON UPDATE CASCADE ; ``` Gives this error: ``` #1452 - Cannot add or update a child row: a foreign key constraint fails (`software`.<result 2 when explaining filename '#sql-c0_54eea'>, CONSTRAINT `Persoons_student_id` FOREIGN KEY (`student_id`) REFERENCES `Student` (`student_id`) ON DELETE CASCADE ON UPDATE CASC) ```

Original source