Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
computers:mysql_tutorial [2015/03/29 20:42]
daniel [InnoDB vs MyISAM]
computers:mysql_tutorial [2015/03/29 21:24] (current)
daniel [Foreign Key]
Line 99: Line 99:
  
 ====== Foreign Key ===== ====== Foreign Key =====
-Foreign key constraints works well all the time only in InnoDB table format (see http://​dev.mysql.com/​tech-resources/​articles/​mysql-enforcing-foreign-keys.html). ​ In case creating a foreign key relation in the child table gives the [[http://​dev.mysql.com/​doc/​refman/​5.6/​en/​error-messages-server.html#​error_er_no_referenced_row_2 | 1452 error]], below sql can help you find exactly which ''​child.id''​ (table ''​child''​ , field ''​id''​) has some rows with info that's or ''​NOT IN''​ in ''​parent.id''​. ​ FYI, ''​NOT IN''​ is faster than doing a ''​LEFT JOIN''​ to find the mismatched info (see http://​stackoverflow.com/​questions/​1253459/​mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa)+Foreign key constraints works well all the time only in InnoDB table format (see http://​dev.mysql.com/​tech-resources/​articles/​mysql-enforcing-foreign-keys.html). ​ In case creating a foreign key relation in the child table gives the [[http://​dev.mysql.com/​doc/​refman/​5.6/​en/​error-messages-server.html#​error_er_no_referenced_row_2 | error 1452]], below sql can help you find exactly which ''​child.id''​ (table ''​child''​ , field ''​id''​) has some rows with info that's or ''​NOT IN''​ in ''​parent.id''​. ​ FYI, ''​NOT IN''​ is faster than doing a ''​LEFT JOIN''​ to find the mismatched info (see http://​stackoverflow.com/​questions/​1253459/​mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa)
  
 <code sql> <code sql>
Line 109: Line 109:
  
 Then you can search for each id, and either delete the row or update them to an id that exists in ''​parent.id''​. Then you can search for each id, and either delete the row or update them to an id that exists in ''​parent.id''​.
 +
 +If you get [[http://​dev.mysql.com/​doc/​refman/​5.6/​en/​error-messages-server.html#​error_er_cannot_add_foreign | error 1215]], then more than likely the data types are a bid different, usually foreign keys are of ''​INT''​ type, indexed, and make sure both are either ''​UNSIGNED''​ (most likely) or ''​SIGNED''​ (not necessary most of the time).
 +
  
 ====== InnoDB vs MyISAM ====== ====== InnoDB vs MyISAM ======