"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
