How To Solwe Diamon Diagram Relation

Hi,

What for a solution is "the best" for diamon relation? Abort one constraint in table 4 and repair with trigger? Or something else?

Description in book online: Cascading Referential Integrity Constraints:

The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree containing no circular references. No table can appear more than once in the list of all cascading referential actions that result from the DELETE or UPDATE. The tree of cascading referential actions must not have more than one path to any given table. Any branch of the tree is terminated when it encounters a table for which NO ACTION has been specified or is the default.

NO ACTION is realy BAD and USELESS solution, when want to delete row in table4 every time ..

Thx a lot


Example:

Use tempdb
go

IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('table4') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE table4
;

IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('table2') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE table2
;

IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('table3') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE table3
;

IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('table1') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE table1
;
go

create table table1 (table1_ID integer not null primary key)

create table table2 (table2_ID integer not null primary key, table1_ID
integer not null)

create table table3 (table3_ID integer not null primary key, table1_ID
integer not null)

create table table4 (table4_ID integer not null primary key, table2_ID
integer not null, table3_ID integer not null)
go

alter table table2 add constraint fk_one foreign key (table1_ID)
references table1 (table1_ID) on delete cascade on update cascade

alter table table3 add constraint fk_two foreign key (table1_ID)
references table1(table1_ID) on delete cascade on update cascade

alter table table4 add constraint fk_three foreign key (table2_ID)
references table2(table2_ID) on delete cascade on update cascade

alter table table4 add constraint fk_four foreign key (table3_ID)
references table3(table3_ID) on delete cascade on update cascade
go