DB2 on delete cascade is not working

[manjuns]

create table A(a1 varchar(10))
create table B(b1 varchar(10), a1 varchar(10) not null)

alter table A add primary key(a1)
alter table B ADD CONSTRAINT "a_fkey" foreign key (a1) references A (a1) on delete cascade

insert into A(A1) values('a1')
INSERT INTO B (b1, A1) VALUES ('b2','a1')

delete from A

I don't see corresponding rows deleted from Table B.

Is there anything wrong i am doing??.. same set of statements is working fine with Postgres.

[cburnett]

You've left out not null on column a1 for table A.

Consequently alter pk fails and fk fails hence no delete cascade.

[Shashank1984]

manjuns,
Here is your testcase....

C:\Documents and Settings\Admini strator>db2 -tvf a.txt

create table A(a1 varchar(10) not null primary key)
DB20000I The SQL command completed successfully.

create table B(b1 varchar(10), a1 varchar(10) references A (a1) on delete cascade)
DB20000I The SQL command completed successfully.

insert into A(A1) values('a1')
DB20000I The SQL command completed successfully.

insert into B(b1, A1) VALUES ('b2','a1')
DB20000I The SQL command completed successfully.

select * from A

A1
----------
a1

1 record(s) selected.


select * from B

B1 A1
---------- ----------
b2 a1

1 record(s) selected.


delete from A
DB20000I The SQL command completed successfully.

select * from A

A1
----------

0 record(s) selected.


select * from B

B1 A1
---------- ----------

0 record(s) selected.