List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:September 11 2003 7:44pm
Subject:Re: Foreign key update and Error :: 1217
View as plain text  
Daevid,

there is really one update you need to do first: upgrade to MySQL-4.0.14.

http://www.innodb.com/ibman.html#InnoDB_foreign_keys
"
Starting from version 3.23.50, you can also associate the ON DELETE CASCADE
or ON DELETE SET NULL clause with the foreign key constraint. Corresponding
ON UPDATE options are available starting from 4.0.8.
"

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for MySQL


----- Original Message ----- 
From: ""Daevid Vincent"" <daevid@stripped>
Newsgroups: mailing.database.myodbc
Sent: Thursday, September 11, 2003 9:10 PM
Subject: RE: Foreign key update and Error :: 1217


> 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.
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/mysql?unsub=1
>


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