Re: ON DELETE CASCADE

[landon kelsey]

the script works on Oracle...the tables are emptied...point made

does NOT work on mysql 5.0...question is WHY NOT?

must be that some setting needs to be made so somebody doesn't blow away a lot of rows and get fired

Hi,


TEST@XE> create table pets_like_boys (pets_name varchar(32), boys_name varchar(32),primary key (pets_name));

Table created.

TEST@XE> create table boys_like_girls (boys_name varchar(32), girls_name varchar(32),primary key (boys_name));

Table created.

TEST@XE> create table girls_like_pets (girls_name varchar(32), pets_name varchar(32),primary key (girls_name));

Table created.

TEST@XE> insert into pets_like_boys values('fluffy', 'sam');

1 row created.

TEST@XE> insert into pets_like_boys values('rover', 'bob');

1 row created.

TEST@XE> insert into pets_like_boys values('skippy', 'joe');

1 row created.

TEST@XE> insert into boys_like_girls values('bob','jill');

1 row created.

TEST@XE> insert into boys_like_girls values('sam','betty');

1 row created.

TEST@XE> insert into boys_like_girls values('joe','sue');

1 row created.

TEST@XE> insert into boys_like_girls values('george','jill');

1 row created.

TEST@XE> insert into girls_like_pets values('sue','fluffy');

1 row created.

TEST@XE> insert into girls_like_pets values('jill','skippy');

1 row created.

TEST@XE> insert into girls_like_pets values('betty','rover');

1 row created.

TEST@XE> alter table pets_like_boys add constraint pets_like foreign key (boys_name) references boys_like_girls on delete cascade;

Table altered.

TEST@XE> alter table boys_like_girls add constraint boys_like foreign key (girls_name) references girls_like_pets on delete cascade;

Table altered.

TEST@XE> alter table girls_like_pets add constraint girls_like foreign key (pets_name) references pets_like_boys on delete cascade;

Table altered.

TEST@XE> select * from pets_like_boys;

PETS_NAME BOYS_NAME
-------------------------------- --------------------------------
fluffy sam
rover bob
skippy joe

TEST@XE> select * from boys_like_girls;

BOYS_NAME GIRLS_NAME
-------------------------------- --------------------------------
bob jill
sam betty
joe sue
george jill

TEST@XE> select * from girls_like_pets;

GIRLS_NAME PETS_NAME
-------------------------------- --------------------------------
sue fluffy
jill skippy
betty rover

TEST@XE> delete from boys_like_girls where boys_name = 'bob';

1 row deleted.

TEST@XE> select * from pets_like_boys;

no rows selected

TEST@XE> select * from boys_like_girls;

no rows selected

TEST@XE> select * from girls_like_pets;

no rows selected


Cheers