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]
