"Daevid Vincent" <[EMAIL PROTECTED]> wrote: > Thanks Victoria for the pointer. I should have looked there first. Duh! > > Now for the help... I tried: > > 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. > > I'm running version: 3.23.56-Max on RedHat 8 via RPM. > > I have data in both these tables, so dropping the tables and redoing schema > is really not a graceful option if I can help it. > > Here are the two tables in their entirety. What have I done wrong? > > And just to clarify, I want this to work such that a change of the > company_table.company_code will trigger the same change to the > rep_table.rep_company_code. > > Ideally I'd also like it to be that if I change the > company_table.company_code, then any other company that has a > company_referal_code will change to the new company_code too... Does that > make sense? Is that possible to have a self referencing foreign key like > that? I thought I read that it isn't possible, but thought I'd ask. 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. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
