Wednesday, February 01, 2006

Porting the EMPLOYEE database from Firebird 2.0 to MySQL 5.1 part 6

More on foreign keys in MySQL:

After all those FK added I can only recommend to check that columns have the same datatype, if this is not true MySQL will spit strange error messages like "can't create table ..." when trying to add foreign keys.

Foreign keys are not deferrable in MySQL so the circular reference between employee and department tables makes impossible to load employee before dept and impossible to load dept before loading employee.
As those tables are innodb i can't issue an "alter table disable keys" so I'll
have to resolve the circular fk relationship between those tables and use triggers to
enforce referential integrity (keys INTEG_61 and INTEG_52 will be deleted and substituded with triggers).
A simpler workaround useful for massive data loads is to issue a "set foreign_key_checks=0" before loading and then a "set foreign_key_checks=1" after load.

No comments: