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"==1)
AND "child" table has record with "id_a"==1
THAN it is not possible to UPDATE or DELETE any rows of "parent"
table, which has "id_a"==1
I hope you can use the following example to repeat the problem and fix
this bug
Thank you
Jan Mazánek
[EMAIL PROTECTED]
#
========================================================================
===========
# Incorrect handling of foreign key constraints in InnoDB tables in
MySQL 3.23.55-max
# run on Windows ME
#
========================================================================
===========
# EXAMPLE
# ============================================================
# Table: zbozi_skupina # parent table
# ============================================================
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=InnoDB;
# ============================================================
# Table: zbozi_skupina_strom # child table
# ============================================================
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=InnoDB;
# ============================================================
# Index: fk_skupina_zbozi_nadrizena
# ============================================================
create index fk_skupina_zbozi_nadrizena on zbozi_skupina_strom
(id_parent asc);
# ============================================================
# Index: fk_skupina_zbozi_podrizena
# ============================================================
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=1 AND
valid_to='2147166000'
# AND/OR: DELETE FROM zbozi_skupina WHERE id=1 AND valid_to='1'
INSERT INTO `zbozi_skupina_strom` ( `id_parent` , `id_child`)
VALUES ('1', '1');
# INCORRECT:
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
# At this point it is NOT possible to:
# 1) DELETE FROM zbozi_skupina WHERE id=1 AND
valid_to='2147166000'
# 2) OR DELETE FROM zbozi_skupina WHERE id=1 AND valid_to='1'
# 3) OR UPDATE zbozi_skupina SET valid_to=2 WHERE id='1' AND
valid_to= '1'
# 4) OR UPDATE zbozi_skupina SET id='2' WHERE id='1' AND
valid_to='1'
#
# 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=='1'.
#
# It should be possible to do anything to one of these two rows!
# (There would still be at least one row having id=='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
