If I had two tables where I need to add a new foreign key to, say:
x
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?)
Advertisement
Answer
You could delete them first?
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.