List:General Discussion« Previous MessageNext Message »
From:SGreen Date:December 22 2005 10:21pm
Subject:Re: Insert fails with Foreign Keys
View as plain text  
"Jesse" <jlc@stripped> wrote on 12/22/2005 05:09:12 PM:

> I'm experimenting with Foreign Keys to maintain referential integrity. I 

> have just added a Foreign Key to one of my tables, but now when I 
attempt to 
> add a new record, I get this error:
> 
> #23000Cannot add or update a child row: a foreign key constraint fails 
> (`fccamp/families`, CONSTRAINT `FK_Campers` FOREIGN KEY (`ID`) 
REFERENCES 
> `campers` (`FamilyID`) ON DELETE CASCADE ON UPDATE CASCADE)
> 
> Did I get something backwards here?  Does a child record have to exist 
> before I can add a parent record?  I thought that a Foreign key referred 
to 
> a child record?  In other words, the "Foreign" component is the child 
table. 
> Is that not right?  If it is, how do I add records?
> 
> Thanks,
> Jesse 
> 

You have to have the parent record first. The FK ensures that the value 
you add to the child table is one of the "valid" values listed in the 
parent table. So with the key you defined, you would have to have a record 
in the `campers` table before you could create a `family` record.  I think 
you defined it backwards because the constraint should be defined on the 
child table, not the parent table. 

I think it should have been defined on the `camper` table like this

...FOREIGN KEY (`familyID`) references family(`id`)...

Hey, I did it too (once a loooong time ago). Don't feel bad.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
Insert fails with Foreign KeysJesse22 Dec
  • Re: Insert fails with Foreign KeysSGreen22 Dec