Hi Evan,

Could you break it up into more tables?

STAFF
id
name

DAY
id
Day

STAFF_ON
id
fk_day
fk_staff

STAFF_OFF
id
fk_day
fk_staff

Now you can cascade deletes without mucking up the RI.

>I am using the RI builder and I have a situation where neither restrict, cascade or ignore is appropriate. Instead I would like to make the foreign key in the child table NULL. The builder does not allow this. To give perspective, here is the simplified example.
>
>I have a table named "staff". The staff table has two, 1 to many relationships with table called "coverage". The coverage table records a day the a staff member is off and records the another staff member that will cover the day. Not surprising the table structure is:
>
>STAFF
>=====
>id
>name
>
>COVERAGE
>=======
>id
>fkStaffOff
>fkStaffOn
>
>The domain expert has told me that if the employee who is being covered is deleted, then just delete the coverage data. If the employee that is covering the shift is deleted then mark the shift as being available. To me this means mark fkStaffOn as NULL, which does not fit into the standard restrict, cascade ignore concepts of RI.
>
>My questions (for those that are still reading).
>1) Is this an indication that my design is flawed
>2) Does this mean I don't understand RI or am misusing RI?
>3) Do I need to roll my own trigger for this case?
>
>Comments and thoughts greatly appreciated.