List:General Discussion« Previous MessageNext Message »
From:Victoria Reznichenko Date:September 11 2003 8:20am
Subject:Re: Foreign key update?
View as plain text  
"Daevid Vincent" <daevid@stripped> wrote:
> Thanks Victoria for the pointer. I should have looked there first. Duh!  
> 
> Now for the help... I tried:
> 
> ALTER TABLE rep_table ADD FOREIGN KEY (`rep_company_code`) REFERENCES
> `company_table` (`company_code`) ON UPDATE CASCADE;
> 
> But get "ERROR 1216: Cannot add a child row: a foreign key constraint fails"

It means that you have row(s) in the child table that doesn't have parent row.

> 
> I'm running version: 3.23.56-Max on RedHat 8 via RPM.
> 
> I have data in both these tables, so dropping the tables and redoing schema
> is really not a graceful option if I can help it.
> 
> Here are the two tables in their entirety. What have I done wrong?
> 
> And just to clarify, I want this to work such that a change of the
> company_table.company_code will trigger the same change to the
> rep_table.rep_company_code.
> 
> Ideally I'd also like it to be that if I change the
> company_table.company_code, then any other company that has a
> company_referal_code will change to the new company_code too... Does that
> make sense? Is that possible to have a self referencing foreign key like
> that? I thought I read that it isn't possible, but thought I'd ask.

You should fix your data first: find rows in the rep_table that don't have parent row in
the the company_table, then add parent row(s) to the company_table for those rows or
delete child row(s) from rep_table. After that you can create FOREIGN KEY with ON UPDATE
CASCADE.



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   Victoria.Reznichenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
       <___/   www.mysql.com




Thread
Foreign key update?Daevid Vincent10 Sep
  • Re: Foreign key update?Victoria Reznichenko10 Sep
    • RE: Foreign key update?Daevid Vincent11 Sep
      • RE: Foreign key update?Daevid Vincent11 Sep
      • Re: Foreign key update?Victoria Reznichenko11 Sep
        • RE: Foreign key update and Error :: 1217Daevid Vincent11 Sep
RE: Foreign key update?Dan Greene10 Sep
Re: Foreign key update and Error :: 1217Heikki Tuuri11 Sep
  • RE: Foreign key update and Error :: 1217 with v4.0.15Daevid Vincent12 Sep
    • Re: Foreign key update and Error :: 1217 with v4.0.15Heikki Tuuri12 Sep
      • RE: Foreign key update and Error :: 1217 with v4.0.15 [SOLVED]Daevid Vincent12 Sep