From: Daevid Vincent
Date: September 11 2003 6:09pm
Subject: RE: Foreign key update and Error :: 1217
List-Archive: http://lists.mysql.com/mysql/149523
Message-Id: <001f01c3788f$ca99f650$8d0aa8c0@gabriel>
MIME-Version: 1.0
Content-Type: text/plain;	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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 =3D 'InteractNetworks, Inc',
company_code =3D '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=20
> that don't have parent row in the the company_table, then add=20
> parent row(s) to the company_table for those rows or delete=20
> child row(s) from rep_table. After that you can create=20
> 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;        =
  =20
+--------+-------------+------------------+
| 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`)=20
> REFERENCES
> > `company_table` (`company_code`) ON UPDATE CASCADE;
> >=20
> > But get "ERROR 1216: Cannot add a child row: a foreign key=20
> constraint fails"
>=20
> It means that you have row(s) in the child table that doesn't=20
> have parent row.