DB2 on delete cascade is not working

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.

re: DB2 on delete cascade is not working

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

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

re: DB2 on delete cascade is not working

manjuns,
Here is your testcase....

C:\Documents and Settings\Administrator>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.