MySQL: how to prevent deletion?

Question

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?

Does this answer your question? [How to write a trigger to abort delete in MYSQL?](https://stackoverflow.com/questions/7595714/how-to-write-a-trigger-to-abort-delete-in-mysql) — MrTux, Apr 15 '21 at 12:10

score 4 · Answer 1 · answered Nov 04 '11 at 21:12

4

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.

answered Nov 04 '11 at 21:12

Aurelio De Rosa

21,856
8
48
71

I can do this for a specific table? Does this require configuring something on the server? Meaning, every time I install this application, I would need to keep doing this? What if I forget? – StackOverflowNewbie Nov 04 '11 at 21:20
@StackOverflowNewbie As far as I know, you can't. Usually you need to configure it on the server. Moreover you have to do this everytime and if you forgot it, the user(s) will be able to delete the row(s). – Aurelio De Rosa Nov 04 '11 at 21:26

score 2 · Answer 2 · answered Jun 27 '17 at 21:25

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/

score 1 · Accepted Answer · answered Nov 04 '11 at 21:23

1

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.

answered Nov 04 '11 at 21:23

Derek Downey

1,512
2
10
16

What does it mean to me if columns cannot be indexed? Is it OK to mix InnoDB and Archive? – StackOverflowNewbie Nov 04 '11 at 21:30
By the way, I used InnoDB so I can enforce referential integrity. What happens to that when I use Archive? – StackOverflowNewbie Nov 04 '11 at 21:32
Archive has no "row cache." What does this mean to me? Also, it says "note that lots of SELECT statements during insertion can deteriorate the compression, unless only bulk or delayed inserts are used" -- what potential problems might this cause me? – StackOverflowNewbie Nov 04 '11 at 21:37
You lose Foreign Key support, but I made the answer in response to this part of the question: "I don't ever want it to be deleted". Not having indexes on a large table could slow the SELECT queries down, which is why it's better to run any analytical queries on a slave. – Derek Downey Nov 04 '11 at 21:40
Do you think using Archive is worth losing referential integrity? How do I set up running queries on a slave? – StackOverflowNewbie Nov 04 '11 at 21:52

score 0 · Answer 4 · answered Nov 04 '11 at 21:13

0

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

answered Nov 04 '11 at 21:13

MadeOfSport

513
1
7
19

I'm trying to protect the table from the application layer. I won't implement anything to delete in this table. However, I want to make sure the table can defend itself no matter what (for example, someone else implements some logic to delete in this table later on -- or someone uses phpMyAdmin and tries to delete something in that table). – StackOverflowNewbie Nov 04 '11 at 21:22

score 0 · Answer 5 · answered Nov 04 '11 at 21:18

0

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.

answered Nov 04 '11 at 21:18

James Black

41,583
10
86
166

Reinserting the record means it would lose the original auto-increment PK it has, right? How does your FK constraint solution work? – StackOverflowNewbie Nov 04 '11 at 21:23
I read the link you provided. The FK constraint solution seems hackish. What do you think? – StackOverflowNewbie Nov 04 '11 at 21:31
Any of these approach are a bit hackish. Preventing people from being able to delete is the correct idea, but you can insert into an auto-increment column as long as it doesn't violate uniqueness. – James Black Nov 04 '11 at 23:05

MySQL: how to prevent deletion?

5 Answers5