Deleting records from tables related by foreign key

Transact-SQL
1

In the transaction database,

I have a table named CustomerDetails which have the table definition below:

( CustomerID BigInt IDENTITY(1,1),

CustomerName Varchar(200),

Address NVarchar(1000),

ContactPhone NVarchar(256),

ContactEmail NVarchar(256)

)

Another table named OrderDetails which have the table definition below:

( OrderID BigInt IDENTITY(1,1),

OrderDescription NVarchar(1000),

CustomerID BigInt)

There is a foreign key relationship between CustomerDetails. CustomerID & OrderDetails. CustomerID.

There are some other tables which are having CustomerID in those tables & having foreign key constraint set on.

So the child tables like OrderDetails needs to be deleted, then only a particular Customer record can be deleted from CustomerDetails.

Is there any query which can be used to delete the child records in tables & then delete the parent recod in parent table ie. CustomerDetails

2

cascade
delete

3

I agree. It is not allowing to delete from parent table, if the parent table record is used in child table.
What i require is a script to DELETE starting from child tables way up to parent table.
If you can give a insight, very helpful.

4

you can google search

image
image991×689 76.7 KB

please see below links

https://www.c-sharpcorner.com/forums/how-to-delete-parent-and-child-records-at-time-in-sql-server

5

Thanks for the reply. I got a link below, where it suggests that you cannot DELETE from two tables ie. parent & child tables in a single query. I hope that is correct.

https://social.msdn.microsoft.com/Forums/en-US/6cfe2f63-b682-4826-a40f-88d6a6efcb4d/delete-both-parent-and-child-table-records-in-one-query?forum=transactsql

Other solution is to set DELETE CASCADE on the table, as you suggested.

Second link below, which suggests that you can set record to NULL, ie. ON DELETE SET NULL

https://studentboxoffice.in/dotnet/article/5/43

So I can conclude that, there is no direct way through writing script, where I can delete child tables first & then finally delete the parent table record.

6

If I am writing script also, I have to write something like this.

delete from table2 where t1column1 = 1

delete from table1 where t1column1 = 1

Table1 is a master table and table2 is a child table.

7

https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql?view=sql-server-ver15

DELETE FROM OrderDetails  
WHERE CustomerID IN   
    (SELECT CustomerID 
     FROM CustomerDetails 
     WHERE SalesYTD > 2500000.00);