List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:September 12 2003 6:33am
Subject:Re: Foreign key update and Error :: 1217 with v4.0.15
View as plain text  
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" <daevid@stripped>
To: <mysql@stripped>
Cc: "'Heikki Tuuri'" <Heikki.Tuuri@stripped>
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:           root@localhost
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 root@localhost"

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:Heikki.Tuuri@stripped]
> Sent: Thursday, September 11, 2003 12:45 PM
> To: mysql@stripped
> 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"" <daevid@stripped>
> 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" <daevid@stripped> 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/mysql?unsub=1
> >
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> unsub=daevid@stripped
>


Thread
Foreign key update?Daevid Vincent10 Sep
  • Re: Foreign key update?Victoria Reznichenko10 Sep
    • RE: Foreign key update?Daevid Vincent11 Sep
      • RE: Foreign key update?Daevid Vincent11 Sep
      • Re: Foreign key update?Victoria Reznichenko11 Sep
        • RE: Foreign key update and Error :: 1217Daevid Vincent11 Sep
RE: Foreign key update?Dan Greene10 Sep
Re: Foreign key update and Error :: 1217Heikki Tuuri11 Sep
  • RE: Foreign key update and Error :: 1217 with v4.0.15Daevid Vincent12 Sep
    • Re: Foreign key update and Error :: 1217 with v4.0.15Heikki Tuuri12 Sep
      • RE: Foreign key update and Error :: 1217 with v4.0.15 [SOLVED]Daevid Vincent12 Sep