problem with Foreign keys using MySQL

May 2nd, 2006, 12:29 pm

Hi there

I am using MySQL 5 , I must say that I don't hand code SQL that is why I use MySQL administrator.

I have creates three tables A B C
A is the main table and two of its fields are Foreign keys coming form B and C

Everything works fine when I add the first key, when I add the second one I get an error:

"Cannot add or update a child row: a foreign key constraint fails"

The wierd thing is Foreign KEY form the table B and C have the same properties
varchar(45) etc .....

Also those properties match the properties of the existing rows from table A , another thing the storage engine is InnoDB

Is adding two FK on a table an issue ? I thought not..

Thanks for looking.

[Rabid Dog]

May 3rd, 2006, 12:17 am

No it is not an issue BUT you cannot do it through the GUI tools

You have to use the ALTER table commands to add the FK. I wrote a tutorial on this as I was struggling with it and finding refs proved to be difficult.

http://www.rabiddog.co.za/tutorials.php -> check under the MySQL heading

May 6th, 2006, 10:21 am

Hi Rabid

Thanks for that , I'll try this.

Chris

[Rabid Dog]

May 6th, 2006, 11:35 am

pleasure

May 7th, 2006, 1:27 am

This has solved my problem

Thanks again

Chris

[Rabid Dog]

May 7th, 2006, 1:43 am

Chris-Duff wrote:

This has solved my problem

Thanks again

Chris

Not to worry mate, I spent a good day trying to figure this one out

May 7th, 2006, 5:00 am

One last thing -- can I add an unlimited amount of foreign keys ?
It worked OK when I added a second foreign key, I try now to add a third one, using the same method, and it throws an error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint

any idea ?

[Rabid Dog]

May 7th, 2006, 7:48 am

When adding FKs remember to make sure that the default value of the column is set to NULL or that both tables are empty. MySql will not create the relationship if the coulmn being added can't insert a value into it that is correct.

If you try and add a FK to a table thta references another table where the Key is coming from and it doesn't have a value matching the referenced table you will get an error as well.

I have had three/four FKs on a single table

May 7th, 2006, 1:44 pm

Rabid, I how you one

I add data in my table, by deleting the data rows it has accepted the statement without throwing any error, it now works fine.


If you visit Scotland give me a shout I will certainly buy you a pint or two

regards

Chris

[Rabid Dog]

May 7th, 2006, 7:30 pm

Chris-Duff wrote:

Rabid, I how you one

I add data in my table, by deleting the data rows it has accepted the statement without throwing any error, it now works fine.


If you visit Scotland give me a shout I will certainly buy you a pint or two

regards

Chris

will do