Geek asks and answers

How do you deal with the elimination of a database entry, which may be FK?

Imagine this setting:

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 ObservationType(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 that this is complete data, it works great. How do you decide when a user wants to delete an observation type? Do you automatically delete any case that has this particular type as FK?

In the real world, how did you deal with this situation?

+3
source share
4 answers

In many cases, this is correct, therefore, ON DELETE CASCADEexists.

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

, , - , ? ? , , - , , .

( ), /. .

+2

? , ? ?

+2

, . , .

, , ,

  • ObservationType
  • logs (ActivityLog?), (). , ( SOX). , , .
0

"" , . , " " . , , update , Deleted 1.

However, if I wanted to delete the rows exactly, I would use the explicit delete operator, not the ON DELETE CASCADEforeign keys.

delete Observation
where ObservationTypeId = 1

delete ObservationType
where ObservationTypeId = 1

I am trying to avoid using implicit ON DELETE CASCADEsince this can lead to hidden dangerous unexpected hits. If the entire database is built on the cascading deletion of a foreign key, someone can mistakenly delete the contents of the entire database by simply deleting one table that is referenced in all other tables. In short, I find ON DELETE CASCADEto solve the error problem.

0
source

More articles:


All Articles