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.
