design decision for DELETE SQL

Have a table called person. which has personid and contactid.

if a person is de开发者_C百科leted then i wish to remove the person from the db and also and related contacts for that person.

Is it better practice to do the DELETES in ONE Store procedure or should my business layer first call the pContactDelete and then call pPersonDelete???????

thanks Niall

Assuming you're using MS SQL Server:

You should probably use ON DELETE CASCADE on your foreign key.

ALTER TABLE Contact
ADD CONSTRAINT FK_PersonContact FOREIGN KEY (PersonId)
REFERENCES Person(PersonId) ON DELETE CASCADE

This will make sure that a contact row is deleted when the person row is deleted. It is a constraint which is always enforced by the database.

You can also set this in SQL Management Studio in the Foreign Key Relationships dialog, INSERT and UPDATE Specification section, Delete Rule -> set to Cascade.

Just make sure that if somewhere you cache your Contact objects in your business layer you refresh your cache as well.

you can define a cascading effect on the your reference key

Any approach you take, data should be in consistent state. It would be simpler if you perform operations on both tables in the same stored procedure. If you put it in your business, I think you should use transaction, so that if your contacts are not deleted, you can rollback deletion of person.

If perchance you're using Oracle you have a couple options.

1. Set up the foreign key between the PERSON and CONTACT tables to cascade deletes.

2. Create an ON DELETE trigger and do the deletes there. This option also gives you the opportunity to create any necessary audit trail entries or write to any log files, etc, which might be necessary when something is deleted.

Share and enjoy.

My approach to that problem is to NOT to use cascade delete constraint as a general principle. Cascading deletes can have bizarre effects when there are cyclic references. In a production environment, its is advisable to handling the deletion of child references through the one stored procedure. There is no need to make this call from Business layer as long as you are not performing any significant validation before deleting the contacts