How to configure reference to be deleted on parent table update?

Question

I have two tables:

info: ID, fee_id

and

fee: ID, amount

and a reference between them (SQL Server 2008):

ALTER TABLE info WITH CHECK ADD CONSTRAINT FK_info_fee FOREIGN KEY(fee_id)
REFERENCES fee (ID)
ALTER TABLE info CHECK CONSTRAINT FK_info_fee
GO

How to configure this reference that way so a record in fee will be deleted if info.fee_id becomes NULL

EDIT: or maybe set info.fee_id to NULL on deleting the corresponding record in fee.

Anyway I can do it this way:

UPDATE info SET fee = NULL WHERE = ..
DELETE FROM fee WHERE ..

but I'm sure that this can be done by the database itself.

Answer 1

You probably don't want to do this. What would you expect to happen if multiple info rows referenced the same fee row?

If you really want to do something like this, adding logic to an AFTER UPDATE, DELETE trigger on the info table would probably be the way to go. Check if any other info rows reference that same fee row, and if not, delete the fee row.

Answer 2

If you really intend to remove rows when fee_id is set to null, one way is an update trigger. In an update trigger, the deleted table contains the old version of the updated rows, and the inserted table contains the new version. By joining them, you can take action when a fee_id changes to null:

CREATE TRIGGER deleteFee
ON info
FOR UPDATE
AS
    DELETE FROM Fee 
    WHERE Fee.id IN (
        SELECT old.fee_id 
        FROM deleted old
        JOIN inserted new ON old.id = new.id
        WHERE old.fee_id = fee.id 
        AND new.fee_id is null
    )

This is tricky when multiple info rows refer to the same fee. The fee will be removed if any info row is set to null. A full synch trigger would avoid that:

CREATE TRIGGER deleteFee
ON info
FOR UPDATE
AS
    DELETE FROM Fee 
    WHERE NOT EXISTS (
        SELECT *
        FROM Info
        WHERE Fee.id = Info.fee_id
    )

But this can have other unintended consequences, like deleting half the Fee table in response to an update. In this case, as in most cases, triggers add more complexity than they solve. Triggers are evil and should be avoided at almost any cost.

Answer 3

Some thoughts:

• If you have a one:one reference then can the 2 tables be combined?
• Drilling up from child to parent is odd: if it's 1:1 then can you reverse the FK direction and simply CASCADE NULL?
• Otherwise, you'll have to use a trigger but assuming 1:1 makes me uneasy...
• ... unless you have a unique constraint/index on info_fee.fee_id

Like so:

ALTER TABLE info WITH CHECK ADD
CONSTRAINT FK_fee_info_fee FOREIGN KEY (id) REFERENCES info_fee (fee_ID) ON DELETE SET NULL