mysql - is it possible to allow delete query only if there is primary key in where clause?

Question

My use-case is to delete a row from the table only with the unique key (or Primary key) to assure that the right data is getting deleted.

I want to prevent delete queries if there are fields other than the Primary key or unique key.

Let's say 'id' is the auto-incremented primary key in my table than,

delete from my_table where id = 10

This should work but,

delete from my_table where first_name = "John"

this should be blocked for all users of MySQL. can we achieve this just with MySQL? I know, I can add multiple checks with my backend language in my logic part but I want to check if it is possible to handle on MySQL part?

question from:https://stackoverflow.com/questions/65859645/is-it-possible-to-allow-delete-query-only-if-there-is-primary-key-in-where-claus

Answer 1

Your approach is flawed because I can trick MySQL into thinking that I am using a primary key like so -- DELETE FROM table WHERE id <> 0;