Delete query would delete all related dependencies

Question

Post reply

Delete query would delete all related dependencies

tilew-948340

Hall of Fame

Points: 3431
More actions
November 21, 2010 at 11:11 am

#243075

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

victorsavinescu SSC Veteran Points: 254 More actions · Answer 1

You think to far. Just make call a DELETE for a SELECT with JOIN clause over all dependent tablas.

😉 Victor S#

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

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

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 3

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"?

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

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.

selectTBL.TABLE_SCHEMAas Constrained_TABLE_SCHEMA

,TBL.TABLE_NAMEas Constrained_TABLE_NAME

,FK.CONSTRAINT_NAME

,FK.UNIQUE_CONSTRAINT_SCHEMA

,FK.UNIQUE_CONSTRAINT_NAME

,RefKey.TABLE_SCHEMAas Referenced_TABLE_SCHEMA

,RefKey.TABLE_NAMEas 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

fromINFORMATION_SCHEMA.TABLE_CONSTRAINTSas TBL

JOININFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTSas FK

on FK.CONSTRAINT_CATALOG= TBL.CONSTRAINT_CATALOG

and FK.CONSTRAINT_SCHEMA= TBL.CONSTRAINT_SCHEMA

and FK.CONSTRAINT_NAME= TBL.CONSTRAINT_NAME

JOININFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGEas 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 + '],'

FROMINFORMATION_SCHEMA.KEY_COLUMN_USAGEFKColumns

WHEREFKColumns.CONSTRAINT_CATALOG= FK.CONSTRAINT_CATALOG

andFKColumns.CONSTRAINT_SCHEMA= FK.CONSTRAINT_SCHEMA

andFKColumns.CONSTRAINT_NAME= FK.CONSTRAINT_NAME

order by FKColumns.ORDINAL_POSITION

FOR XML PATH('')

) FKC ( FKColumnList )

CROSS APPLY

(SELECT'[' + UQColumns.COLUMN_NAME + '],'

FROMINFORMATION_SCHEMA.KEY_COLUMN_USAGEUQColumns

WHEREUQColumns.CONSTRAINT_CATALOG= FK.UNIQUE_CONSTRAINT_CATALOG

andUQColumns.CONSTRAINT_SCHEMA= FK.UNIQUE_CONSTRAINT_SCHEMA

andUQColumns.CONSTRAINT_NAME= FK.UNIQUE_CONSTRAINT_NAME

order by UQColumns.ORDINAL_POSITION

FOR XML PATH('')

) UQC ( UQColumnList )

SQL = Scarcely Qualifies as a Language

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 5

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!