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