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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-26 : 09:42:10
|
| Anil writes "In Oracle, the default is to prevent the deletion of a primary key record if the primary key is responsible for child records. The only way to delete it would be to remove the child records in the foreign key and then delete the primary key.This is not the default behavior of SQL Server. I deleted the primary key record as a test (not good data thankfully!) and the delete cascaded through all the associated records in the foreign key. There has to be a way around this, doesn't there?Thanks for all of the help." |
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2001-11-26 : 09:52:48
|
| When I've tried to do what you said before, I would get an error in SQL stating something along the lines of "could not delete... associated key in ..."Make sure you don't have a trigger set up on the primary table for deletes. If you do, then that might be what is cascading the deletes to the other tables. |
 |
|
|
andre
Constraint Violating Yak Guru
259 Posts |
Posted - 2001-11-26 : 09:56:56
|
quote:
This is not the default behavior of SQL Server. I deleted the primary key record as a test (not good data thankfully!) and the delete cascaded through all the associated records in the foreign key. There has to be a way around this, doesn't there?
You must have SQL Server 2000, which has cascade deletes as one of the new features. I mainly work on SQL Server 7, so I don't know how to disable cascade deletes. However, I'm sure you can disable it somewhere in Enterprise Manager. |
|
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2001-11-26 : 10:04:25
|
| I checked in BOL on this and there is a way to turn it off.In design view go to Table and Index Properties.Go to Relationships.Uncheck Cascade Delete Related RecordsThe exact topic in BOL is "Administration Tools FAQ" |
|
|
|
|
|
|
Prevent deletion of child records (Relationship)