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
> >
>
>
