user1221765 user1221765 - 22 days ago 7
SQL Question

Delete from multiple table with foreign keys

I am trying to delete records from my main table, while running the script I understood, that the Id in my main table is referenced as Foreign Keys in 8 other tables. I don't want to use CASCADE DELETE as I have to alter the table constraints. My Main table is called Job, and its Primary key is 'Id', which is referenced as foreign key 'JobId' in the other tables.

How do I delete the Foreign key reference records in the related table before deleting from the main Job table.

here is my below code. the commented code is just a prediction.

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

First, you should look out for all relationships and then you could just run a DELETE statement against the referenced tables based on the Keys (IDs) from your main query. A basic idea could be:

-- 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)

Hope it helps.