Jan, this is a design deficiency which was known to me, but was not documented. I will remove it in some version 4.0.xx.
Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-4.0 from http://www.mysql.com sql query ----- Original Message ----- From: <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Thursday, March 13, 2003 11:13 AM Subject: Incorrect handling of foreign key constraints in InnoDB tables in MySQL 3.23.55-max run on Windows ME > Hi, > > If a "parent" table has multicolumn primary key > AND "child" table have a constraint to one column of this "parent" > primary key (let's say column "id_a") > AND there are more records in "parent" table having the same value > of "id_a" (let's say "id_a"=3D=3D1) > AND "child" table has record with "id_a"=3D=3D1 > THAN it is not possible to UPDATE or DELETE any rows of "parent" > table, which has "id_a"=3D=3D1 > > I hope you can use the following example to repeat the problem and fix > this bug > > Thank you > > Jan Maz=E1nek > [EMAIL PROTECTED] > > # > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > # Incorrect handling of foreign key constraints in InnoDB tables in > MySQL 3.23.55-max > # run on Windows ME > # > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > # EXAMPLE > > # = > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > # Table: zbozi_skupina # parent table =20 > # = > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > create table zbozi_skupina > ( > id INTEGER default '1' not > null AUTO_INCREMENT, > valid_to INTEGER default > '2147166000' not null, > valid_from INTEGER default '1' not > null, > akce VARCHAR(64) default > 'zbozi_vypis' not null, > constraint pk_zbozi_skupina primary key (id, valid_to) > )TYPE=3DInnoDB; > > # = > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > # Table: zbozi_skupina_strom # child table =20 > # = > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > create table zbozi_skupina_strom > ( > id_parent INTEGER default '1' not > null, > id_child INTEGER default '1' not > null, > constraint pk_zbozi_skupina_strom primary key (id_parent, id_child) > )TYPE=3DInnoDB; > > > # = > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > # Index: fk_skupina_zbozi_nadrizena =20 > # = > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > create index fk_skupina_zbozi_nadrizena on zbozi_skupina_strom > (id_parent asc); > > > # = > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > # Index: fk_skupina_zbozi_podrizena =20 > # = > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > create index fk_skupina_zbozi_podrizena on zbozi_skupina_strom (id_child > asc); > > > alter table zbozi_skupina_strom > add constraint fk_zbozi_sk_sz_rodic_zbozi_sk foreign key (id_child) > references zbozi_skupina (id); > > > alter table zbozi_skupina_strom > add constraint fk_zbozi_sk_sz_dite_zbozi_sk foreign key (id_parent) > references zbozi_skupina (id); > > > > ------------------------------------------------------------------------ > ---- > INSERT INTO `zbozi_skupina` ( `id`, `valid_to`, `valid_from`, > `akce`) > VALUES ( '1', '2147166000', '1', > 'zbozi_vypis'); > > INSERT INTO `zbozi_skupina` ( `id`, `valid_to`, `valid_from`, > `akce`) > VALUES ( '1', '1', '1', > 'zbozi_vypis'); > > # Correct: > # At this point it is possible to: > # DELETE FROM zbozi_skupina WHERE id=3D1 AND > valid_to=3D'2147166000' > # AND/OR: DELETE FROM zbozi_skupina WHERE id=3D1 AND valid_to=3D'1' > > INSERT INTO `zbozi_skupina_strom` ( `id_parent` , `id_child`)=20 > VALUES ('1', '1'); > > > # INCORRECT: > !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! > # At this point it is NOT possible to: > # 1) DELETE FROM zbozi_skupina WHERE id=3D1 AND > valid_to=3D'2147166000' > # 2) OR DELETE FROM zbozi_skupina WHERE id=3D1 AND valid_to=3D'1' > # 3) OR UPDATE zbozi_skupina SET valid_to=3D2 WHERE id=3D'1' AND > valid_to=3D '1'=20 > # 4) OR UPDATE zbozi_skupina SET id=3D'2' WHERE id=3D'1' AND > valid_to=3D'1'=20 > # > # MySQL reports: Cannot delete a parent row: a foreign key > constraint fails > # It is not true! > # Constraints are defined only for column zbozi_skupina.id AND > # there are two rows having id=3D=3D'1'. > # > # It should be possible to do anything to one of these two rows! > # (There would still be at least one row having id=3D=3D'1') > # > # It should be possible to update zbozi_skupina.valid_to for BOTH > of these rows! > # (There is no impact on zbozi_skupina.id) > # > # I think that constraints in MySQL behave in this incorrect way: > # If there is a child table having constraint to the row which is > tried to be DELETEd/(UPDATEd primary key) > # MySQL returns error > # > # The correct behavior should be: > # If there is a child table having constraint to the row which is > tried to be DELETEd/(UPDATEd primary key) > # CHECK if there exists another row with the same part of primary > key > # OR CHECK if after UPDATE the constraint will be OK > # IF above checks fails, THAN report error > > # I tried to solve this problem with adding indexes on every > single column, but it didn't help > # I hope you can use the above example to repeat the problem and > fix this bug > > # Thank you, Honza, [EMAIL PROTECTED] > > > > # P.S.: If you use ON DELETE CASCADE, than the rows of child table > are deleted. No matter, that there still exists > # "parent" table with the constrained value of the key (id) > > > > > > --------------------------------------------------------------------- > 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 > --------------------------------------------------------------------- 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
