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 |
|
fantasma
Starting Member
8 Posts |
 Posted - 2005-09-21 : 04:50:54
|
| Let's say I have 3 tables... myTable2 and myTable3 both have foreign keys that reference the primary key of myTable1 and I want to delete an entry in myTable1 without it resulting in a foreign key constraint conflict.Disregarding all other options is it conceptually better to use an INSTEAD OF DELETE trigger leaving the relationship enforced, or not to enforce the relationship and use an AFTER DELETE trigger that attempts to maintain RI between these particular tables? I think I'm going with the AFTER DELETE to resolve problems that could occur with a CMS that is in use here if I were to use INSTEAD OF, but I have been wondering about this for future reference.As a seperate issue, is there an SQL Server mechanism that I could call to clean up the database FK's that reference non-existent records in the PK table should the AFTER triggers fail, or is a query the only way to go to perform this? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-21 : 05:05:11
|
| >>I want to delete an entry in myTable1 without it resulting in a foreign key constraint conflict.Why do you want delete Master records when child records exists?Have you looked ON DELETE CASCADE in Books On Line?MadhivananFailing to plan is Planning to fail |
 |
|
|
fantasma
Starting Member
8 Posts |
Posted - 2005-09-21 : 05:42:10
|
If I can avoid it I'd much prefer not to have to delete a master record where a child record exists, but the background to this is very tangled... I'll try to give a brief explanationThere's a db that is almost a merging of 2 seperate systems. These include a CMS and tables that relate to it, which has been supplied to us, and additional tables as a result of development and statistical analysis which aren't covered as part of the CMS.Much of the RI for the CMS tables is performed by the CMS itself, as such there is very little done by the db other than storage for the CMS, which hasn't made for great maintainability or development. This has resulted in the RI for the additional tables needing to be performed outside of the CMS. Being as I can't alter the workings of the CMS the way I can maintain RI is in the database itself. It's not ideal to have it mixed in this way but there's no other option, unfortunately.Anyway, the CMS tables don't cascade deletes and being as this problem relates to FK Constraint conflicts between one of the CMS tables and the additional tables I don't want to cascade deletes from it in case it effects the CMS. So, the choice is a trigger... or... well, that's it to be honest.Are you still awake?  |
|
|
|
|
|
|
|
|
Referential Integrity Question