CODEWITHSUNDEEP
sqlsql-servert-sql
BrooklynDev
BrooklynDev

Reputation: 890

Adding a constraint or otherwise preventing the removal of a record in a SQL DB table

I have a table in a SQL Server database that contains a row I never want deleted. This is a row required by the application to work as designed.

Is there a way to add a constraint to that row that prevents it from being deleted? Or another way to handle this scenario?

Upvotes: 1

Views: 3408

Answers (3)

Mike Chamberlain
Mike Chamberlain

Reputation: 42440

Here is an example of using a FOR DELETE trigger to prevent the deletion of a row when a certain condition is satisfied:

CREATE TRIGGER KeepImportantRow ON MyTable
  FOR DELETE
AS BEGIN
    -- This next line assumes that your important table has a
    -- column called id, and your important row has an id of 0.
    -- Adjust accordingly for your situation.
    IF DELETED.id = 0 BEGIN
        RAISERROR('Cannot delete important row!', 16, 1)
        ROLLBACK TRAN
    END
END

Upvotes: 4

Remus Rusanu
Remus Rusanu

Reputation: 294237

If you want to prevent accidental deletes then you could have a dummy table that declares a foreign key into your table with ON DELETE NO ACTION, and add one row in it with the foreign key matching your 'precious' row primary key. This way if the 'parent' row is deleted, the engine will refuse and raise an error.

If you want to prevent intentional deletes then you should rely on security (deny DELETE permission on the table). Of course, privileged users that have the required permission can delete the row, there is no way to prevent that, nor should you try. Since SQL Server does not support row level security, if you need to deny only certain rows then you have to go back to the drawing broad and change your table layout so that all rows that have to be denied are stored in one table, and rows that are allowed to be delete are stored in a different table.

Other solutions (like triggers) will ultimately be a variation on these themes, what you really must solve is the question whether you want to prevent accidental deletes (solvable) or intentional deletes (unsolvable, is their database, not yours).

Upvotes: 3

Andrew
Andrew

Reputation: 27294

You could do it in a number of ways, although it depends on the situation.

If the table only contains that row, do not grant deletion / truncate privledges.

If the table contains other rows as well you could use a before deletion trigger.

One issue you will have is that someone with DBA / SA access to the database, can get around anything you put in, if they desire, so what are you trying to protect against, casual user, or anyone.

Upvotes: 1

Related Questions