Setting up foreign keys to match MS Access relationships

[Murray Muspratt-Rouse]

I migrated MS Access tables to MySQL using the Migration Toolkit/Utility. I am now left with the problem (opportunity!) of defining foreign keys to reproduce MS Access relationships, so as to enable cascaded deletes. I have one parent table that provides the unique key link to multiple child tables, some one-to-one, some one-to-many. The MySQL Server version is MySQL 4.1.12a-nt via TCP/IP, and the client is MySQL Client Version 5.0.11 for Windows XP.

I have tried adding a foreign key link between the parent table and one of the child tables using MySQL Administrator, specifying CASCADE for both Delete and Update. This has resulted in error 1216 'Cannot add or update a child row, a foreign key constraint fails'. The same error results if I specify Update RESTRICT or NO ACTION. If I specify Update SET NULL I get error no. 1005 - 'Cant create table 'schema name\#sql5cc_56.frm'.

Help! I am not even sure whether I should be defining foreign keys on the parent or the child tables. What I want is for all rows in the child tables to be deleted when a row is deleted in the parent, in the same way as in MS Access.

Murray