CODEWITHSUNDEEP
mysqlkeyconstraintsinnodb
user1902689
user1902689

Reputation: 1775

MySQL InnoDB - Constraints for keys linking parent to one of two children

Using MySQL, moving from MyISAM to InnoDB tables. Database design started with dumping the data and re-importing it without foreign keys or constraints. Adding those one at a time to find errors.

I have ParentTable which can either be linked to ChildTableA or ChildTableB, but not both. Should the CREATE syntax be: (using CREATE syntax for simplicity rather than multiple ALTERs)

CREATE TABLE `ParentTable`
   `IDParentTable` bigint(20) unsigned NOT NULL auto_increment,
   `IDChildTableA` bigint(20) unsigned NOT NULL default '0',
   `IDChildTableB` bigint(20) unsigned NOT NULL default '0',
   PRIMARY KEY (`IDParentTable`),
   KEY `ParentTable_IDChildTableA` (`IDChildTableA`),
   KEY `ParentTable_IDChildTableB` (`IDChildTableB`)
ENGINE=InnoDB DEFAULT CHARSET=latin1;

Without thinking about it, I tried including:

CONSTRAINT `ParentTable_IDChildTableA` FOREIGN KEY (`IDChildTableA`) REFERENCES `ChildTableA` (`IDChildTableA`),
CONSTRAINT `ParentTable_IDChildTableB` FOREIGN KEY (`IDChildTableB`) REFERENCES `ChildTableB` (`IDChildTableB`)

Which failed, because many rows have 0 for IDChildTableA, and many rows have 0 for IDChildTableB. But, no rows have 0 for both. It's seeing that no ChildTableA exists with IDChildTableA of 0, and likewise with B.

Is there a proper way to handle this situation while keeping referential integrity? Without splitting ParentTable in two? A way to say it's OK if it's 0 or references a valid related table? Or, does wanting polymorphic tables mean I have to go without constraints? BTW, I much prefer this route than having a single IDChildTable foreign key and then having another column designating whether it's table A or B... Not how I see that would work either for constraints, just saying I prefer not to go that route...

Upvotes: 0

Views: 322

Answers (1)

spencer7593
spencer7593

Reputation: 108370

A column used as a foreign key can be nullable. Use a NULL value in the foreign key column to indicate "no row referenced."

It seems like you have your foreign keys backwards. Usually, the child table has a reference to the parent table.

parent (id int primary key) 

childA (id int,  parent_id int, ...)

childB (id int,  parent_id int, ...)

EDIT

Related to the question regarding a foreign key column referencing two tables (based on a discriminator column)... that's not possible. A foreign key constraint can reference only one table.

To get something like that work, you'd need to add two separate foreign key columns, each referencing one target table. You could make use of the extra discriminator column (A or B) to identify which foreign key column should be used, so one fk column would be populated with a reference, the other fk column would be set to NULL.

However, there is no declarative constraint that would require exactly one of those two fk columns to be populated. That would not be enforced by the database. The extra discriminator column would actually be redundant, because you could derive that based on which foreign key column was populated.

Upvotes: 1

Related Questions