CODEWITHSUNDEEP
mysqldatabaseinnodb
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40653

MySQL: how to prevent deletion?

Once a record is entered in a particular table, I think I don't ever want it to be deleted. The records are there to keep track of things for historical purposes. I'm using MySQL, InnoDB. Is there any way I can protect that table from accidental deletes by the application layer? If so, is this a good approach?

Upvotes: 7

Views: 7635

Answers (5)

user2021201
user2021201

Reputation: 380

I use a trigger that detects the deletion and does some illegal stuff so the whole operation fails. For example like this:

CREATE TRIGGER protect_delete before delete ON protected_table 
FOR EACH ROW UPDATE non_existing_table SET non_existing_column=1;

So when someone will attempt a delete operation - the whole statement will fail. You also might use better names for non_existing_column and non_existing_table.

E.g it is possible to obtain an error message like this:

ERROR 1146 (42S02): Table 'database.delete_restricted_on_tableX' doesn't exist

EDIT: also it is possible to create even better fail messages, please check here http://www.brokenbuild.com/blog/2006/08/15/mysql-triggers-how-do-you-abort-an-insert-update-or-delete-with-a-trigger/

Upvotes: 3

Derek Downey
Derek Downey

Reputation: 1532

One other option is switch to the ARCHIVE engine for historical tables.

This will prevent any DELETE or UPDATE actions on the table, and compress the data. One (major) downside to this approach is that you cannot index the columns on the table.

Upvotes: 1

James Black
James Black

Reputation: 41858

You may want to write a trigger that detects the deletion, then reinserts the record, but there may be some issues with that, so you can also just add a foreign key constraint that will prevent the deletion.

For some discussions on this you can look at: http://rpbouman.blogspot.com/2011/10/mysql-hacks-preventing-deletion-of.html.

Upvotes: 0

MadeOfSport
MadeOfSport

Reputation: 513

I think you should implement this logic in your application layer and insert a column where you put a no-delete flag.

Another idea would be to exclude the delete access for the db user

Upvotes: 0

Aurelio De Rosa
Aurelio De Rosa

Reputation: 22162

If you can set the permission for your user(s), don't give the delete permission. There're situations where this practice is a "must be" like the tables for statistics purpose. Thus, if your table is used to achieve one of this goal, it's a good approach.

Upvotes: 4

Related Questions