Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike83
Starting Member
2 Posts |
 Posted - 2009-12-22 : 07:12:13
|
| Hi guysDoes anyone have a tool or a method for deleting records from tables which have foreign key references in other tables without deletingthe referenced records one after the other.Thanks. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-12-22 : 07:27:40
|
| Cascading delete maybe? |
 |
|
|
mike83
Starting Member
2 Posts |
Posted - 2009-12-23 : 06:03:54
|
| Thanks RickD. I think thats what I need. |
|
|
|
behrman
Yak Posting Veteran
76 Posts |
Posted - 2009-12-25 : 21:37:42
|
| In the design of the foreign key you can specify a delete rule. Like you said the CASCADE option is what you need. The following is from BOL:ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/08812343-e9a6-4a0f-91fd-80d95ab4b71f.htmINSERT and UPDATE Specification CategoryExpand to show information for the Delete Rule and the Update Rule for the relationship.Delete RuleSpecify what happens if a user tries to delete a row with data that is involved in a foreign key relationship: No Action An error message tells the user that the deletion is not allowed and the DELETE is rolled back.Cascade Deletes all rows containing data involved in the foreign key relationship.Set Null Sets the value to null if all foreign key columns for the table can accept null values. Applies to SQL Server 2005 only.Set Default Sets the value to the default value defined for the column if all foreign key columns for the table have defaults defined for them. Applies to SQL Server 2005 only.RAQ Report: Web-based Excel-like Java reporting tool |
|
|
|
|
|
|
Deleting Records in Referenced Tables