Ah! So what I had to do was then... show create table rep_table;
alter table rep_table drop foreign key 0_359; ALTER TABLE rep_table ADD FOREIGN KEY (`rep_company_code`) REFERENCES `company_table` (`company_code`) ON UPDATE CASCADE; It all works now. Man, talk about good timing 'eh? A few months ago I wouldn't be able to do this huh ;-) > -----Original Message----- > From: Heikki Tuuri [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 11, 2003 11:33 PM > To: Daevid Vincent; [EMAIL PROTECTED] > Subject: Re: Foreign key update and Error :: 1217 with v4.0.15 > > > Daevid, > > CONSTRAINT `0_359` FOREIGN KEY (`rep_company_code`) REFERENCES > `company_table` (`company_code`) > > you do not have ON UPDATE CASCADE in your constraint. MySQL > versions < 4.0.8 > ignored it. > > 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]> > To: <[EMAIL PROTECTED]> > Cc: "'Heikki Tuuri'" <[EMAIL PROTECTED]> > Sent: Friday, September 12, 2003 4:01 AM > Subject: RE: Foreign key update and Error :: 1217 with v4.0.15 > > > Heikki, I took your advice and installed the RPM updates to no avail: > > [root]# rpm -qa | grep "mysql" -i > MySQL-server-4.0.15-0 > mod_auth_mysql-1.11-1 > MySQL-devel-4.0.15-0 > MySQL-shared-4.0.15-0 > php-mysql-4.1.2-7.2.6 > MySQL-client-4.0.15-0 > MySQL-shared-compat-4.0.15-0 > > And it seems to be running... > > mysql> \s > -------------- > mysql Ver 12.21 Distrib 4.0.15, for pc-linux (i686) > > Connection id: 1 > Current database: mysql > Current user: [EMAIL PROTECTED] > SSL: Not in use > Current pager: stdout > Using outfile: '' > Server version: 4.0.15-standard > Protocol version: 10 > Connection: Localhost via UNIX socket > Client characterset: latin1 > Server characterset: latin1 > UNIX socket: /tmp/mysql.sock > Uptime: 1 min 53 sec > > Also, I noticed that my phpinfo() shows "Client API version = > 3.23.56" -- is > that normal? > But oddly enough, my phpMyAdmin page shows "MySQL > 4.0.15-standard running on > localhost as [EMAIL PROTECTED]" > > Anyways, back to the real problem... > > Updating via my web page produced the error 1217, so thinking that the > PHP/mySQL version mentioned above might be a factor, I just > logged into the > mysql CLI program (Ver 12.21 Distrib 4.0.15, for pc-linux (i686)) and > exectued the query directly there. However I still get the > same error... > > mysql> UPDATE company_table SET company_code = 'bunk1234' > WHERE company_id = > '91' LIMIT 1; > ERROR 1217: Cannot delete or update a parent row: a foreign > key constraint > fails > > I looked at some InnoDB status log that phpMyAdmin has, and > it shows some > debug info that doesn't make sense to me. It complains that there is a > record, but of course there is a record. That's the record I want to > update?! > > ------------------------ > LATEST FOREIGN KEY ERROR > ------------------------ > 030911 17:41:07 Transaction: > TRANSACTION 0 913922, ACTIVE 0 sec, process no 24665, OS > thread id 36874 > updating or deleting, thread declared inside InnoDB 499 > mysql tables in use 1, locked 1 > 4 lock struct(s), heap size 320, undo log entries 1 > MySQL thread id 1, query id 3118 localhost root Updating > UPDATE company_table SET company_code = 'bunk1234', > Foreign key constraint fails for table crimson/rep_table: > , > CONSTRAINT `0_359` FOREIGN KEY (`rep_company_code`) REFERENCES > `company_table` (`company_code`) > Trying to delete or update in parent table, in index > company_code tuple: > 0: len 8; hex 62756e6b34333231; asc bunk4321;; 1: len 3; hex > 00005b; asc > ..[;; > But in child table crimson/rep_table, in index > rep_company_code, there is a > record: > RECORD: info bits 0 0: len 8; hex 62756e6b34333231; asc > bunk4321;; 1: len 2; > hex 0001; asc ..;; > > > > -----Original Message----- > > From: Heikki Tuuri [mailto:[EMAIL PROTECTED] > > Sent: Thursday, September 11, 2003 12:45 PM > > To: [EMAIL PROTECTED] > > Subject: Re: Foreign key update and Error :: 1217 > > > > > > 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/mysql?> [EMAIL PROTECTED] > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
