sql - 使用外键从多个表中删除

Question

我正在尝试从我的主表中删除记录，同时运行我理解的脚本，我的主表中的 ID 在其他 8 个表中被引用为外键。我不想使用 CASCADE DELETE，因为我必须更改表约束。我的主表名为 Job，其主键为“Id”，在其他表中被引用为外键“JobId”。

如何在从主作业表中删除之前删除相关表中的外键引用记录。

这是我下面的代码。注释代码只是一个预测。

SELECT * FROM [JOB] j WHERE Name=@Name AND Title=@Title AND Zip=@Zip
                    AND Id<>@Id AND NOT EXISTS (SELECT * FROM NewJob nj WHERE J.Id=nj.Id)                           
                --DELETE FROM [Table1]  a WHERE a.JobId = j.Id AND
                --DELETE FROM [Table2]  F WHERE f.JobId = j.Id AND
                --DELETE FROM [Table3] jct WHERE jct.JobId = j.Id AND
                --DELETE FROM [Table4]  jch WHERE jch.JobId = j.Id AND
                --DELETE FROM [Table5]  jedu WHERE jedu.JobId = j.Id AND
                --DELETE FROM [Table6]  jexp WHERE jexp.JobId = j.Id AND
                --DELETE FROM [Table7]  jflc WHERE jflc.JobId = j.Id AND
                --DELETE FROM [Table8]  usj WHERE usj.JobId = j.Id AND
DELETE FROM [JOB] WHERE Id IN (SELECT Id FROM [JOB] WHERE Name=@Name AND Title=@Title AND Zip=@Zip AND Id<>@Id)

Answer 1

首先，您应该查看所有关系，然后您可以根据主查询中的键 (ID) 对引用的表运行 DELETE 语句。一个基本的想法可能是:

-- how many tables and references? Just to be sure.
EXEC sp_fkeys 'Job'

-- required query for deleting relationships
DELETE FROM ForeignTable WHERE JobId IN (SELECT Id FROM Job WHERE [....])
DELETE FROM ForeignTable2 WHERE JobId IN (SELECT Id FROM Job WHERE [....])

-- main query
DELETE FROM [JOB] WHERE Id IN (SELECT Id FROM [JOB] WHERE Name=@Name AND Title=@Title AND Zip=@Zip AND Id<>@Id)

希望对你有帮助。