MySQL Forums
Forum List  »  Microsoft Access

Setting up foreign keys to match MS Access relationships
Posted by: Murray Muspratt-Rouse
Date: October 08, 2005 05:40AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Setting up foreign keys to match MS Access relationships
7012
October 08, 2005 05:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.