0

I have a database table that want to protect some rows from deleting and updating. I am using MySQL database. How can fix it in phpmyadmin or sql syntax?

EX: prevent row with id:18 from delete and update:

enter image description here

Taylor Gomez
  • 320
  • 1
  • 5
  • 22
  • I use of this sql: `CREATE TABLE preserve_permissions ( permission_id INT PRIMARY KEY, FOREIGN KEY (permission_id) REFERENCES permissions (permission_id) ); INSERT INTO perserve_permissions (permission_id) VALUES (1234);` but i have error: Error `SQL query: CREATE TABLE preserve_permissions( permission_id INT PRIMARY KEY , FOREIGN KEY ( permission_id ) REFERENCES permissions( permission_id ) ); MySQL said: #1005 - Can't create table 'mymy.preserve_permissions' (errno: 150) (Details...) ` – Taylor Gomez Feb 19 '13 at 18:48
  • You will have to adapt the code in that question to suit your specific needs and situation. It should be used as a template and not copied verbatim. The KEY and FOREIGN KEY need to be of the same data type. I do believe that is the source of this error. – Buggabill Feb 19 '13 at 18:56

1 Answers1

1

You have various options.

SESSION WISE: if you are using INNODB

SET AUTOCOMMIT=0;
UPDATE table SET mainpage =1 WHERE id = 18;
-- do NOT commit until you want to release the lock.
-- if you do that under myisam, 
-- I think it is going to lock the whole table (to be verified)

STRUCTURAL PROTECTION

Create a trigger before update/delete on your table and raise an error when :new.id = 18

Sebas
  • 21,192
  • 9
  • 55
  • 109
  • I copy above code in "Run SQL query/queries on database mymy:" and get error: `Error SQL query: UPDATE TABLE SET mainpage =1 WHERE id =18; MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table SET mainpage =1 WHERE id = 18' at line 1 ` – Taylor Gomez Feb 19 '13 at 18:51
  • 1
    Are you telling me you didn't change the table name? – Sebas Feb 19 '13 at 18:55
  • Well, I was wrong. I run it in box sql on phpmyadmin and get in same box as: `SET AUTOCOMMIT=0;# MySQL returned an empty result set (i.e. zero rows). What is the purpose of trigger? UPDATE access_level SET mainpage =1 WHERE id = 18;# MySQL returned an empty result set (i.e. zero rows).` What is the purpose of trigger? – Taylor Gomez Feb 19 '13 at 19:40
  • Please explain more clearly? – Taylor Gomez Feb 19 '13 at 19:44
  • The trigger is an optional option. This would be to ensure that if somebody modifies the row you want, there would be an error and the modification/deletion would fail. – Sebas Feb 19 '13 at 19:54
  • @Sebas Given that you can only trigger after delete, this wouldn't prevent deletion. – iheanyi Dec 11 '18 at 23:19