In the transaction database,
I have a table named CustomerDetails which have the table definition below:
( CustomerID BigInt IDENTITY(1,1),
Another table named OrderDetails which have the table definition below:
( OrderID BigInt IDENTITY(1,1),
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
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.
you can google search
please see below links
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.
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
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.
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.
DELETE FROM OrderDetails
WHERE CustomerID IN
WHERE SalesYTD > 2500000.00);