Cannot Create Pk/relationship In Mysql

[deux_ex_machina]

Hi,

When I tried to create PK/Relationship using SQLyog I always get the error 1216 “Cannot Add or Update a Child row: foreign key constraint fails”.

Thanks,

Deux

[peterlaursen]

This is sprobably because the tables contain data.  And the FK you define are conflicting with the data!

example:

If 'child' (table `orders') has a column CustomerId pointing to the ID column of 'parent' (table `customers`) and if `customers`.`ID` holds the data '1','2','3' and '4' you cannot define a PK if `orders`.`CustomerId` hold other data than  '1','2','3' and '4'.

A Foreing Key is a CONSTRAINT on data.  there can only be data in 'child' that are in 'parent' too.

You could try starting with empty tables!

[deux_ex_machina]

peterlaursen wrote on Jan 21 2007, 09:34 PM:

This is sprobably because the tables contain data.  And the FK you define are conflicting with the data!

example:

If 'child' (table `orders') has a column CustomerId pointing to the ID column of 'parent' (table `customers`) and if `customers`.`ID` holds the data '1','2','3' and '4' you cannot define a PK if `orders`.`CustomerId` hold other data than  '1','2','3' and '4'.

A Foreing Key is a CONSTRAINT on data.  there can only be data in 'child' that are in 'parent' too.

You could try starting with empty tables!

Hi Pete,

Thanks for the reply I tried what you have said, I even tried having the fields Null and Not Null “all fields are empty” but still I'm getting this problems.

Deauxii

[peterlaursen]

let us se a structure-only dump for the tables and the SQL (from history) what you are trying to do and/or a screendump.

[Author]

Posts