Delete from multiple table with foreign keys

Question

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 1

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.

Answer 2

Something like this will probably be easiest if you create a temporary table to store the id(s) that you want to delete after you discover them from the main table. Do it all in a transaction too, to maintain ACID properties.

Begin Transaction
Create Table #JobIDsToDelete (JobID int);
Insert Into #JobIDsToDelete 
Select ID
  From [Job] As j
  Left Join NewJob As nj On j.ID = nj.ID
  Where Name = @Name
    And Title = @Title
    And Zip = @Zip
    And ID <> @ID
    And nj.ID Is Null;

Delete From Table1 Where JobID In (Select JobID From #JobIDsToDelete);
Delete From Table2 Where JobID In (Select JobID From #JobIDsToDelete);
...
Delete From [Job] Where ID In (Select JobID From #JobIDsToDelete);
Commit Transaction