InnoDB foreign keys bug
am 07.02.2003 00:25:12 von Scott Wong
Version: Mysql 4.0.10-gamma
Description: Innodb fails to follow the foreign key rules after alter =
table.
This bug can be done in several ways.
How to repeat
These variations gives same errors.
1st bug example:
drop table if exists parent;
drop table if exists child;
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=3DINNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON UPDATE CASCADE
) TYPE=3DINNODB;
insert into parent set id =3D 1;
insert into child set id=3D1, parent_id=3D1;
alter table child add FOREIGN KEY (`parent_id`) REFERENCES =
`test.parent` (`id`) ON DELETE CASCADE ;
update parent set id=3D2 where id=3D1;
gives :ERROR 1217: Cannot delete or update a parent row: a foreign key =
constraint fails
end 1st bug.
Variations of this bug :
drop table if exists parent;
drop table if exists child;
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=3DINNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
ON UPDATE CASCADE
) TYPE=3DINNODB;
insert into parent set id =3D 1;
insert into child set id=3D1, parent_id=3D1;
alter table child add FOREIGN KEY (`parent_id`) REFERENCES =
`test.parent` (`id`) ON UPDATE CASCADE ;
update parent set id=3D2 where id=3D1;
ERROR 1217: Cannot delete or update a parent row: a foreign key =
constraint fails
delete from parent;
The intesting thing is this :
show create table child;
FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON UPDATE =
CASCADE,
FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE =
ON UPDATE CASCADE
fix
?
Thank you for your time
Scott Wong
Meiko America, INC
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread13714@lists.mysql.com
To unsubscribe, e-mail
Re: InnoDB foreign keys bug
am 07.02.2003 01:30:56 von Heikki Tuuri
Scott,
I would like to declare this as a 'feature'. You should not define multiple
foreign key constraints on the same foreign key/referenced key pair.
I could, of course, add an error message if someone tries to do that.
The algorithm in ON UPDATE CASCADE and ON DELETE CASCADE is this: InnoDB
takes a single declared constraint, tries to update or delete child rows as
instructed in that constraint, and checks that other constraints are
satisfied.
Also, I recommend not to define contradictory ON UPDATE actions. The
following is an example of such:
CREATE TABLE t (a INT NOT NULL, PRIMARY KEY (a),
FOREIGN KEY (a) REFERENCES t2 (a) ON UPDATE CASCADE,
FOREIGN KEY (a) REFERENCES t2 (b) ON UPDATE CASCADE) TYPE =
InnoDB;
Now if someone UPDATEs both a and b in t2, what should we do?
Regards,
Heikki
Innobase Oy
sql query
..........................
Subject: InnoDB foreign keys bug
From: Scott Wong
Date: Thu, 6 Feb 2003 15:25:12 -0800
------------------------------------------------------------ ----------------
----
Version: Mysql 4.0.10-gamma
Description: Innodb fails to follow the foreign key rules after alter table.
This bug can be done in several ways.
How to repeat
These variations gives same errors.
1st bug example:
drop table if exists parent;
drop table if exists child;
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent id INT, INDEX par ind (parent id),
FOREIGN KEY (parent id) REFERENCES parent(id)
ON UPDATE CASCADE
) TYPE=INNODB;
insert into parent set id = 1;
insert into child set id=1, parent id=1;
alter table child add FOREIGN KEY (`parent id`) REFERENCES `test.parent`
(`id`)
ON DELETE CASCADE ;
update parent set id=2 where id=1;
gives :ERROR 1217: Cannot delete or update a parent row: a foreign key
constraint
fails
end 1st bug.
Variations of this bug :
drop table if exists parent;
drop table if exists child;
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent id INT, INDEX par ind (parent id),
FOREIGN KEY (parent id) REFERENCES parent(id)
ON DELETE CASCADE
ON UPDATE CASCADE
) TYPE=INNODB;
insert into parent set id = 1;
insert into child set id=1, parent id=1;
alter table child add FOREIGN KEY (`parent id`) REFERENCES `test.parent`
(`id`)
ON UPDATE CASCADE ;
update parent set id=2 where id=1;
ERROR 1217: Cannot delete or update a parent row: a foreign key constraint
fails
delete from parent;
The intesting thing is this :
show create table child;
FOREIGN KEY (`parent id`) REFERENCES `parent` (`id`) ON UPDATE CASCADE,
FOREIGN KEY (`parent id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON
UPDATE
CASCADE
fix
?
Thank you for your time
Scott Wong
Meiko America, INC
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread13715@lists.mysql.com
To unsubscribe, e-mail
Re: InnoDB foreign keys bug
am 07.02.2003 17:29:28 von Scott Wong
Hi Heikki,
I can see your point of view with multiple foreign key constraints on =
same foreign key/referenced key pair.
I came across this 'feature' because I actually wanted to execute =
something like this:
drop table if exists parent;
drop table if exists child;
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=3DINNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON UPDATE CASCADE
) TYPE=3DINNODB;
and then after some data entry .. i also wanted ON DELETE CASCADE in the =
constraint of that pair.
As it stands i dont think innodb supported ALTER TABLE CHANGE/DROP =
FOREIGN KEYS
and not wanting to create a new table and renaming i just issued this =
command expecting it to work:=20
alter table child add FOREIGN KEY (`parent_id`) REFERENCES =
`test.parent` (`id`) ON DELETE CASCADE ;
although valid i agree that this presents a problem in other terms =
indeed.=20
I think this will also present a problem when people try to do similar =
things and run into this 'feature'.
I ran into it wanting to upgrade from the mysql server version 3 that =
has the ON DELETE CASCADE defined.
I suppose i could stop the foreign key check, create a table with BOTH =
UPDATE CASCADE=20
and DELETE CASCADE declared first, select the data from old, delete old, =
rename to new, start foreign key check. Just seems kind of tedious for =
wanting to add / remove a foreign key constraint.
Fix?
Maybe add an error code for multiple foreign key constraints on same =
foreign key
and have another alter table CHANGE/DROP command for redefining foreign =
key constraints.
Regards,
Scott Wong
Meiko America, INC
----------------------
From: Heikki Tuuri=20
Date: Fri, 7 Feb 2003 02:30:56 +0200=20
Scott,
I would like to declare this as a 'feature'. You should not define =
multiple
foreign key constraints on the same foreign key/referenced key pair.
I could, of course, add an error message if someone tries to do that.
The algorithm in ON UPDATE CASCADE and ON DELETE CASCADE is this: InnoDB
takes a single declared constraint, tries to update or delete child rows =
as
instructed in that constraint, and checks that other constraints are
satisfied.
Also, I recommend not to define contradictory ON UPDATE actions. The
following is an example of such:
CREATE TABLE t (a INT NOT NULL, PRIMARY KEY (a),
FOREIGN KEY (a) REFERENCES t2 (a) ON UPDATE CASCADE,
FOREIGN KEY (a) REFERENCES t2 (b) ON UPDATE CASCADE) TYPE =3D
InnoDB;
Now if someone UPDATEs both a and b in t2, what should we do?
Regards,
Heikki
Innobase Oy
sql query
..........................
Subject: InnoDB foreign keys bug
From: Scott Wong
Date: Thu, 6 Feb 2003 15:25:12 -0800
------------------------------------------------------------ -------------=
---
----
Version: Mysql 4.0.10-gamma
Description: Innodb fails to follow the foreign key rules after alter =
table.
This bug can be done in several ways.
How to repeat
These variations gives same errors.
1st bug example:
drop table if exists parent;
drop table if exists child;
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=3DINNODB;
CREATE TABLE child(id INT, parent id INT, INDEX par ind (parent id),
FOREIGN KEY (parent id) REFERENCES parent(id)
ON UPDATE CASCADE
) TYPE=3DINNODB;
insert into parent set id =3D 1;
insert into child set id=3D1, parent id=3D1;
alter table child add FOREIGN KEY (`parent id`) REFERENCES =
`test.parent`
(`id`)
ON DELETE CASCADE ;
update parent set id=3D2 where id=3D1;
gives :ERROR 1217: Cannot delete or update a parent row: a foreign key
constraint
fails
end 1st bug.
Variations of this bug :
drop table if exists parent;
drop table if exists child;
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=3DINNODB;
CREATE TABLE child(id INT, parent id INT, INDEX par ind (parent id),
FOREIGN KEY (parent id) REFERENCES parent(id)
ON DELETE CASCADE
ON UPDATE CASCADE
) TYPE=3DINNODB;
insert into parent set id =3D 1;
insert into child set id=3D1, parent id=3D1;
alter table child add FOREIGN KEY (`parent id`) REFERENCES =
`test.parent`
(`id`)
ON UPDATE CASCADE ;
update parent set id=3D2 where id=3D1;
ERROR 1217: Cannot delete or update a parent row: a foreign key =
constraint
fails
delete from parent;
The intesting thing is this :
show create table child;
FOREIGN KEY (`parent id`) REFERENCES `parent` (`id`) ON UPDATE =
CASCADE,
FOREIGN KEY (`parent id`) REFERENCES `parent` (`id`) ON DELETE CASCADE =
ON
UPDATE
CASCADE
fix
?
Thank you for your time
Scott Wong
Meiko America, INC
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread13719@lists.mysql.com
To unsubscribe, e-mail
Re: InnoDB foreign keys bug
am 08.02.2003 13:58:43 von Sinisa Milivojevic
Scott Wong writes:
>
> Version: Mysql 4.0.10-gamma
>
> Description: Innodb fails to follow the foreign key rules after alter table.
> This bug can be done in several ways.
>
> How to repeat
>
> These variations gives same errors.
> 1st bug example:
>
> drop table if exists parent;
> drop table if exists child;
> CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
> CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
> FOREIGN KEY (parent_id) REFERENCES parent(id)
> ON UPDATE CASCADE
> ) TYPE=INNODB;
> insert into parent set id = 1;
> insert into child set id=1, parent_id=1;
> alter table child add FOREIGN KEY (`parent_id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE ;
> update parent set id=2 where id=1;
> gives :ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails
>
> end 1st bug.
>
> Thank you for your time
>
> Scott Wong
> Meiko America, INC
Hi!
This is a known behaviour, which will be fixed in the future.
For the moment being only drop / create works, while alter does not.
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com
Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread13722@lists.mysql.com
To unsubscribe, e-mail