T-SQL – Adding FK to table, make it do drop/null/efault automagically?

If I had two tables where I need to add a new foreign key to, say:

ALTER TABLE [release_user] 
    ADD CONSTRAINT [user_release] 
        FOREIGN KEY ([user_id]) REFERENCES [user_table] ([user_id]) 
                ON DELETE CASCADE

If there are records in the release_user table that do not have a matching ID in the user_table, the command will fail. I expect that.

However, is there a way to create the FK, and have it automatically delete (DELETE CASCADE) the records that would cause the FK to fail?

(Or, for example, if I used DELETE SET NULL, would null out the record’s field in the release_user table?)

Answer

You could delete them first?

DB_FIDDLE

delete from ru
from release_user ru
left join user_table ut
on ut.user_id = ru.user_id
where ut.user_id is null

Then, add your constraint.

Advertisement

Answer