| Author |
Topic |
|
sql777
Constraint Violating Yak Guru
314 Posts |
 Posted - 2005-02-17 : 14:42:02
|
| Hi guys (thanks a bunch for all your help all the time btw, great community!).So I have a bunch of tables that have CustomerID referenced in them, and I want to delete the customer row in tblCustomers and hence have to delete all other references of customerID FK in the other tables. The application can be on either sql7.0 or sql2000. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-17 : 14:45:07
|
| Well cascading deletes isn't available in SQL Server 7.0, so you won't be able to use that option. You can write a delete trigger on the tblCustomers table that handles the delete on all of the child tables.Tara |
 |
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-02-17 : 15:25:23
|
| ok wow! I didn't realize how complicated this is going to be.If I delete one table, it might have child tables that have to be deleted, and so on. And I have to watch the order in which I delete something.this is going to be a nightmare!Example:TableCountryTableCityTableOrganizationsTableBankAccountsrelOrganizationsBankAccountsrelCountriesOrganizationsetcIf I delete a country, I have to delete its cities and organizations except if an organization is associated with another Country, in that case I only delete the relation. But if the organization has only one relatation i.e the country I am deleting, then I have to delete the organization also!my application has quite a few of those things mentioned above going on at the same time!!!maybe its better just to leave all the above data in the database, but just put a flag on the tableCountry table that signifies that it is deleted?? |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-02-18 : 23:35:38
|
| does this mean my db design is poor if its a real head scratcher to figure out the deletion order? |
|
|
|
Auric
Yak Posting Veteran
70 Posts |
Posted - 2005-02-19 : 00:02:02
|
| sql777, like any other programming the database has a data flow. I think you best draw it out and find the safest order and the deletion criteria.Select * from users where clue > 0 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-19 : 02:34:05
|
| Creating an ERD of your database will help you out. I use Visio for this. If you don't have a good ERD tool, then you can just use the diagramming option inside Enterprise Manager.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-19 : 04:23:30
|
This is probably way off beam, but if its a struggle to set up triggers to perform the cascaded deletes, how about just flagging the associated records as "deleted"? - that can happen in any order. So long as your "select" stuff excludes rows flagged as "deleted" this will be fine - then you can have a batch job that deletes the flagged ones, or just leave them there for "auditing purposes"  . If the referentiual integrity is complicated, your Delete Batch job can just keep looping round, ignoring errors, until it can't delete anything any more. (You may need to restrict deleted to small batch sizes for this to actually work properly if the R.I. is really hairy)Kristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-02-19 : 04:34:42
|
| cascading deletes sounds like a good thing but it can lead to some real trouble. I don't like seeing data deleted, Kristen's point on flagging something as deleted (or active/inactive) is probabaly a better way to go. It is easier to undo a mistake, which will make your life as a dba much simpler.-ec |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-02-19 : 09:39:35
|
| Great idea, I actually noticed a product I had do that once, it told me it doesn't actually delete records because its just too messy..hehe.so i was thinking of that myself, I guess I can delete 2 tables of mine as those have the most data in them, the others are likely to be under 1000 records for years to come.suffice is to say, i'd rather program in the 'flag' field than figure this mess out! (not to mention the potential for errors if I make a mistake, and even if I don't make a mistake with my deletes..what about updates to the code that might break it in the future..ahhh!!)cascading thanks to all! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-19 : 10:52:34
|
You might want to stick a VIEW where the original table was, and change the name of the "real" table, and have the VIEW exclude the flagged records. Thus any existing programs won;t be able to, accidentally, show the "deleted" records:EXEC sp_rename 'MyOriginalTableName', 'MyHiddenTableName'GO-- Now add a "MyFlagField" column, with a DEFAULT constraint of (0) and NOT NULLCREATE VIEW MyOriginalTableNameASSELECT *FROM MyHiddenTableNameWHERE MyFlagField = 0 Kristen |
|
|
|
|
cascading deletes