RE: sql table referential integrity

The closest I could come was a foreign key constraint. I could get the delete working but not the update. You'd have to put a trigger on that but the timing on that might be insurmountable.

create table rob.order_header (
primary_key int as identity not null primary key,
overflow_key_1 char (10),
overflow_key_2 char (10),
Description varchar(200) allocate(25) not null,
constraint x1 unique (overflow_key_1, overflow_key_2)
)
;
create table rob.order_header_overflow (
primary_key int as identity not null primary key,
overflow_key_1 char (10) not null,
overflow_key_2 char (10) not null,
ExtraColumn varchar(200) allocate(25) not null,
constraint x2 foreign key (overflow_key_1, overflow_key_2) references rob.order_header (overflow_key_1, overflow_key_2)
on delete cascade
-- not supported: on update cascade
)
;
insert into rob.order_header (overflow_key_1, overflow_key_2, description)
values('A', 'A', 'test')
;
insert into rob.order_header_overflow (overflow_key_1, overflow_key_2, extraColumn)
values('A', 'A', 'bravo')
;
update rob.order_header set overflow_key_1='B', overflow_key_2='B' where overflow_key_1='A';
-- SQL State: 23504 Vendor Code: -531 Message: [SQL0531] Update prevented by referential constraint X2 in ROB. Cause . . . . . : Constraint X2 in ROB identifies the table being updated as the parent table in a relationship with dependent table ORDER00002 in ROB, with an update rule of RESTRICT or NO ACTION. The update of a parent key is prevented when there are rows in the dependent table with matching values. Recovery . . . : In order to perform this update, you must either drop the constraint, or delete the rows in the dependent table that are dependent on this row.

select primary_key from rob.order_header;
-- returns a 1

delete from rob.order_header where primary_key = 1;

select * from rob.order_header_overflow;
-- no rows returned. Got deleted.

Rob Berendt