List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:September 11 2003 6:09pm
Subject:RE: Foreign key update and Error :: 1217
View as plain text  
I really appreciate your help with this.

Hmmm... So, while SET FOREIGN_KEY_CHECKS allowed me to ALTER the table, now
I can't UPDATE the company that has a rep with the same company_code
because:

Error :: 1217 :: Cannot delete a parent row: a foreign key constraint fails
UPDATE company_table SET company_name = 'InteractNetworks, Inc',
company_code = 'TEST1234', ...

However, I can update a company that doesn't have a company_code set (and so
no reps either).
I can also manually change the rep_company_code.
But once again, if I try to change the company that has a code that a rep
also has the same code, I get that error 1217 above.

> 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.

I don't follow you here.  I don't think I have rows in the child (rep) table
that don't have a match in the parent (company) table.

mysql> select rep_id, rep_fname, rep_company_code from rep_table;           
+--------+-------------+------------------+
| rep_id | rep_fname   | rep_company_code |
+--------+-------------+------------------+
|      1 | Daevid      | bunk4321		|
|      5 | Test        | interact         |
|      8 | Mark        | interact         |
|      9 | Joe	     | interact         |
 [ snip ]
|     10 | Tony        | interact         |
|     15 | Bob         | interact         |
|     17 | John        | interact         |
+--------+-------------+------------------+
17 rows in set (0.00 sec)

There are holes because some reps were deleted.

But as I'm just building this portion of the CRM, I have only used
"interact" and "bunk4321" as the rep_company_code and also in the
company_table as company_code. I had set these manually in each table.

mysql> select company_id, company_name, company_code from company_table
limit 5;
+------------+-------------------------+--------------+
| company_id | company_name            | company_code |
+------------+-------------------------+--------------+
|          7 | City Of Seattle         |              |
|          8 | Port of Seattle         |              |
|          9 | Test Company            |              |
|         10 | Metropolitian 		   |              |
|         11 | DIO, Inc.               |              |
|         91 | Bunk Company            | bunk4321     |
  [snip]
|        276 | InteractNetworks, Inc   | interact     |
+------------+-------------------------+--------------+

> -----Original Message-----
> "Daevid Vincent" <daevid@stripped> wrote:
> >
> > 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.


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