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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
