Description:
I create two tables with the second table having a constraint to the first. However, I'm able to add invalid entries to the second table (the constraint to the first table doesn't exist). In addition, if I have valid entries in both tables and delete the entry in the first table, the entry that should be deleted in the second persists.
I'll include a script that can be run interactively to demonstrate this issue.
How to repeat:
--#########################################################
-- table definitions
--#########################################################
drop table IF EXISTS consTable1 ;
create table consTable1(
tbl_id int PRIMARY KEY AUTO_INCREMENT,
name varchar(32) NOT NULL,
descr varchar(80) NOT NULL DEFAULT '',
INDEX nameidx (name)
) TYPE=MyISAM ;
drop table IF EXISTS consTable2 ;
create table consTable2 (
tbl_id int,
deviceid int(10) unsigned NOT NULL,
custid varchar(30) NOT NULL,
INDEX scriptidx (tbl_id),
INDEX deviceidx (deviceid),
INDEX custidx (custid),
INDEX compound(custid, deviceid),
UNIQUE INDEX triindex(tbl_id, deviceid, custid),
CONSTRAINT cs FOREIGN KEY (tbl_id) REFERENCES consTable1( tbl_id ) ON DELETE CASCADE )
TYPE=MyISAM ;
--#########################################################
-- SAMPLE INSERT
--#########################################################
INSERT INTO consTable1( name, descr) VALUES ( 'myName', 'myDesc') ;
--#########################################################
-- show that it worked
--#########################################################
SELECT * FROM consTable1 ;
--#########################################################
-- Note that you do NOT define the tbl_id --
--#########################################################
--#########################################################
-- HOWEVER It *is* necessary to define it for the insert into the consTable2 table
--#########################################################
INSERT INTO consTable2( tbl_id, deviceid, custid) VALUES ( 1, 1, 'customerId') ;
--#########################################################
-- show that it worked
--#########################################################
SELECT * FROM consTable2 ;
--#########################################################
-- this insert shouldn't work because the tbl_id -2- doesn't
-- exist in consTable1
--#########################################################
INSERT INTO consTable2( tbl_id, deviceid, custid) VALUES ( 2, 1, 'should not be possible') ;
--#########################################################
-- show that it worked anyway
--#########################################################
SELECT * FROM consTable2 ;
--#########################################################
-- now delete the valid entry from consTable1
-- the constrained entry in consTable2 should also be deleted
--#########################################################
delete from consTable1 where tbl_id = 1 ;
--#########################################################
-- show that it worked
--#########################################################
SELECT * FROM consTable1 ;
--#########################################################
-- show that it failed to delete the entry in consTable2
--#########################################################
SELECT * FROM consTable2 ;
--#########################################################
-- The following sql statement shows those invalid (orphaned) data.
--#########################################################
select consTable2.tbl_id from consTable2 left outer join consTable1 on consTable2.tbl_id = consTable2.tbl_id
where consTable1.tbl_id is null ;
-- Can someone please explain this behavior?
