0

How can I prevent from removing a default Row in MySql ?

Assume, I have a record with 0 id, user can not insert any record with 0 id value, in some situation I use the default row value to show default setting. now I want to prevent from removing this row by user in Database Level.

I tried to use triger to do this, for example I checked in before delete the id value , if the id==0, then id=4444444..., in this situation no record will be deleted, because I set id with a non exists row id. But this method does not work;

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Behrouz.M
  • 3,445
  • 6
  • 37
  • 64
  • Why are you hardcoding ID value.. playing with ID is not good – Shekhar_Pro Feb 27 '11 at 08:47
  • @Shekhar_Pro : what is your suggestion ? – Behrouz.M Feb 27 '11 at 08:49
  • Please don't ask the same question twice. This is an exact duplicate of http://stackoverflow.com/questions/5132356/how-to-reject-delete-statement-in-before-delete-triger-mysql/5132594#5132594 –  Feb 27 '11 at 10:32

1 Answers1

2

You could create a table that uses a foreign key constraint referencing your ID column (provided that it is the primary key of your table). Then insert a row into that "child" table that references ID = 0 and remove all update/delete privileges for your user from that child table.

Now if someone tries to delete the row with ID = 0 in the "parent" table that will fail due to an existing child row in the other table.

Using this pattern, you can "protect" any ID in the parent table from being deleted.