November 21, 2010 at 11:11 am
Hi
If I want to delete a person in "AdventureWorks2008R2" data base, I know I have to make sure that all dependencies to that person are removed before I can do it.
Because "person.person" has many dependencies and may have 7 levels of dependencies (like for [Sales].[SalesOrderHeaderSalesReason]), what would be the best way to do it?
Is there a TSQL command that I can add to the command DELETE that would take care of deleting all dependencies?
USE AdventureWorks2008R2
DELETE [TSQLcommandForDependency] FROM Person.Person WHERE BusinessEntityID = 1
November 21, 2010 at 2:45 pm
You think to far. Just make call a DELETE for a SELECT with JOIN clause over all dependent tablas.
😉 Victor S#
November 21, 2010 at 3:28 pm
I know I have to make sure that all dependencies to that person are removed before I can do it.
Not necessarily - the foreign key constraints specification will include action to take on the dependent table on a delete of the parent that includes
1) No action - disallow the delete action if there are any dependent rows
2) Cascade - delete the dependent rows
3) Set Null - set the foreign key values to null in the dependent rows and then delete.
So just run a delete command and if it fails report the error.
If a delete actions is incorrect, change the action to the appropriate action.
SQL = Scarcely Qualifies as a Language
November 21, 2010 at 4:25 pm
Carl Federl (11/21/2010)
I know I have to make sure that all dependencies to that person are removed before I can do it.Not necessarily - the foreign key constraints specification will include action to take on the dependent table on a delete of the parent that includes
It is not the answer I was hoping, but it is an answer of what I have to do now: changing all relationships on my tables in my DB!!
Is there a way to change the constraints on all the tables with a single command?
Is there also a configuration I can change on a DB so, if I create new tables, the default would be set to "cascade" instead of "no action"?
November 23, 2010 at 8:08 am
Please realize that foreign key delete action need to be specified based on the business rules and a global approach of changing all of the actions to "cascade" is not appropriate.
There are frequently delete business rule that are more complex than can be supported by the delete actions and need to be implemented in "instead of triggers".
From Books OnLine:
A table that has an INSTEAD OF trigger cannot also have a REFERENCES clause that specifies a cascading action. An AFTER trigger on a table targeted by a cascading action, however, can execute an INSERT, UPDATE, or DELETE statement on another table or view that fires an INSTEAD OF trigger defined on that object.
Here is a SQL Statement to list all of the Foreign key contraints with the referenced table and constraint. A comma delimited column with the FK columns and referenced columns is included.
select TBL.TABLE_SCHEMA as Constrained_TABLE_SCHEMA
, TBL.TABLE_NAME as Constrained_TABLE_NAME
, FK.CONSTRAINT_NAME
, FK.UNIQUE_CONSTRAINT_SCHEMA
, FK.UNIQUE_CONSTRAINT_NAME
, RefKey.TABLE_SCHEMA as Referenced_TABLE_SCHEMA
, RefKey.TABLE_NAME as Referenced_TABLE_NAME
, FK.MATCH_OPTION
, FK.UPDATE_RULE
, FK.DELETE_RULE
, LEFT(FKC.FKColumnList,LEN(FKC.FKColumnList) - 1 ) as Foreign_Key_Column_List
, LEFT(UQC.UQColumnList,LEN(UQC.UQColumnList) - 1 ) as Referenced_Key_Column_List
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as TBL
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as FK
on FK.CONSTRAINT_CATALOG = TBL.CONSTRAINT_CATALOG
and FK.CONSTRAINT_SCHEMA = TBL.CONSTRAINT_SCHEMA
and FK.CONSTRAINT_NAME = TBL.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE as RefKey
on RefKey.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
and RefKey.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
and RefKey.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
CROSS APPLY
(SELECT '[' + FKColumns.COLUMN_NAME + '],'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE FKColumns
WHERE FKColumns.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
and FKColumns.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
and FKColumns.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
order by FKColumns.ORDINAL_POSITION
FOR XML PATH('')
) FKC ( FKColumnList )
CROSS APPLY
(SELECT '[' + UQColumns.COLUMN_NAME + '],'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE UQColumns
WHERE UQColumns.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
and UQColumns.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
and UQColumns.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
order by UQColumns.ORDINAL_POSITION
FOR XML PATH('')
) UQC ( UQColumnList )
SQL = Scarcely Qualifies as a Language
November 23, 2010 at 7:48 pm
OK!... I realize that there is much more things to think about before changing all table property to "cascade on delete". So I think I will ask for rules and wait a little to know more before doing it...
:-D!
Thank you very very much for your help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply