How do you deal with eliminating a database record which may be a FK?

Imagine this setup:

create table ObservationType 
(
ObservationTypeId int primary key identity(1,1),
Name nvarchar(32) not null
)

create table Observation
(
ObservationId int primary key identity(1,1),
ObservationTypeId int foreign key references Observati开发者_Python百科onType(ObservationTypeId),
Title nvarchar(32) not null,
Description nvarchar(1024) not null,
StudentId int foreign key references Student(StudentId)
)

create table Student
(
 foo bar
)

Now imagine this is full of data, it's working fine. How do you deal when the user wants to delete an observation type? Do you automatically delete any observation that has that particular type as FK?

In the real world, how did you handle this situation?

In many cases, that's the right thing to do - that's why ON DELETE CASCADE exists.

This means that for the deleted row, any row in another table that is defined as such a foreign key on that row will also be deleted.

The question that you need to ask is whether it is something that the user will really want? Does it make sense within the application domain to perform such deletes? Ask your user/s what they expect to happen if they delete an observations type - what do they think should happen to the existing observations.

Sometimes, it is better to go with a soft delete (marking a row with a delete status in a status field), so you can recover/undelete. This is not without its share of problems.

add a cascade delete to the foreign key? also, do you really want to allow real deletion from your database? why not just mark them as inactive?

Though it exists and can be useful, I wouldn't recommend setting cascade delete. I do agree, however, that soft-deletes can be a good thing.

However, for what you want, why not create a procedure that

1. deletes the appropriate Observation records
2. deletes the ObservationType
3. logs (ActivityLog?) what has been deleted and why (a comment). Don't need to store entire set of deleted data, but just enough for compliance (aka SOX). Who, what, when.

I prefer using 'soft' deletes if possible. Which means having Deleted bit column on a table. So, when you delete you would actually run update statement on the table to set Deleted to 1.

However, if I wanted to delete rows for sure, I would use explicit delete statement, rather than ON DELETE CASCADE foreign keys.

delete Observation
where ObservationTypeId = 1

delete ObservationType
where ObservationTypeId = 1

I try to avoid using implicit ON DELETE CASCADE as it can lead to hidden dangerous unexpected deletes. If all database is built on cascade delete foreign key, somebody by mistake can delete content of whole database just by running delete against one table that is referenced in all other tables. So to put it shortly I find ON DELETE CASCADE to be a bug prone solution.