design decision for DELETE SQL

Question

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

if a person is deleted 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

Answer 1

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.

Answer 2

you can define a cascading effect on the your reference key

Answer 3

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.