There are 2 tables A and B with A being the parent of B.
Table A
(
Col1 varchar(5) Not Null
)
Table B
(
B_PK varchar(5) Not Null ,
B1 varchar(5) ,
B2 varchar(5)
)
Basically what we want to do was if any A.Col1 is deleted and there
are matching B1 or B2, set them to null for those records.
I know if we use 'on delete' with rules other than 'SET NULL', it
would be fine. But what is wrong with setting any related FK values to
null when its corresponding PK is deleted??
D:\DBA\LDS>db2 create table A (Col1 varchar(5) not null)
DB20000I The SQL command completed successfully.
D:\DBA\LDS>db2 alter table A add primary key (col1)
DB20000I The SQL command completed successfully.
D:\DBA\LDS>db2 create table B (B_PK varchar(5) not null, B1
varchar(5), B2 varchar(5))
DB20000I The SQL command completed successfully.
D:\DBA\LDS>db2 alter table B add primary key (B_PK)
DB20000I The SQL command completed successfully.
D:\DBA\LDS>db2 alter table B add foreign key (B1) references A on
delete set null
DB20000I The SQL command completed successfully.
D:\DBA\LDS>db2 alter table B add foreign key (B2) references A on
delete set null
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0632N FOREIGN KEY "B2..." is not valid because the table cannot be
defined
as a dependent of table "LDSDEV.A" because of delete rule restrictions
(reason
code = "3"). SQLSTATE=42915
