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"" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
