List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:September 12 2003 6:34pm
Subject:RE: Foreign key update and Error :: 1217 with v4.0.15 [SOLVED]
View as plain text  
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:Heikki.Tuuri@stripped] 
> Sent: Thursday, September 11, 2003 11:33 PM
> To: Daevid Vincent; mysql@stripped
> 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" <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