Creating immutable field in mysql

Question

I would like to make the TIMESTAMP DEFAULT CURRENT_TIMESTAMP field for the purpose of creation. But if someone or something changes, then TIMESTAMP, my data will not be consistent.

Is there a way that I can guarantee that it will not change if I do not delete the line and insert it except at the application level?

---

With the suggested answer, I could work with something like this

 CREATE TRIGGER consistency1 BEFORE UPDATE ON table1 FOR EACH ROW BEGIN IF NEW.creationtime != OLD.creationtime THEN SET NEW.creationtime = OLD.creationtime; END IF; END; 

Answer 1

Since my comment has been appreciated, here is an extended version.

I personally do not think this is possible.

Anyway, there are a few things you can try:

• Make sure that only the application can write to the database

• Write such a trigger (pseudo code!)

   create trigger prevent_change_timestamp on tbl_name before update #fetch old row value #verify if the timestamp field has been changed #raise an error (any SQL error will do) 

• Or like that

   create trigger revert_change_timestamp on tbl_name after update #fetch pre-change row value #update the row with the "old" value in place of the new one 

I would personally go with the 3rd option, if possible. In any case, the second is also good. I did not rely on option 1 if it was not necessary (for example: there is no access to trigger functionality)

Additional information here: link

Answer 2

This is ridiculous in the sense that database applications do not offer this functionality as standard: not only for the “created” timestamp field, but for things like the auto-increment identifier field, and any different values ​​that you might want to set when creating record, and then never let change ... I wonder what is the rationale?

Answer 3

I tried this in MySQL 5.1 and got an error

 DELIMITER // CREATE TRIGGER member_update_0 -> AFTER UPDATE ON members -> FOR EACH ROW -> BEGIN -> IF NEW.id != OLD.id THEN -> SET NEW.id = OLD.id; -> END IF; -> END;// 

ERROR 1362 (HY000): Newline update not allowed after trigger

Adopted the same trigger with LAST change to BEFORE; for me it's a counter-intuitive way to do this, but it works

 delimiter ; UPDATE members SET id=11353 WHERE id=1353; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 

Answer 4

What you can do here, you can write TRIGGER in the table when the row is updated. In this trigger, you can compare old and new values, and if they are different, you can simply overwrite the new value with the old.

Answer 5

This can actually be done very carefully if you use InnoDB.

Create another table with one column. This column must have a foreign key (hence the innodb requirement in this solution), pointing to the immutable column of the source table.

Put a restriction like "ON UPDATE RESTRICT".

In short:

 CREATE TABLE original ( .... immutable_column ... INDEX index1(immutable_column) .... ) ENGINE=INNODB; CREATE TABLE restricter ( ..... col1, INDEX index2(col1), FOREIGN KEY (col1) REFERENCES original (immutable_colum) ON UPDATE RESTRICT ON DELETE CASCADE ) ENGINE=INNODB; 

Answer 6

Based on one more step (for those of us who still adhere to the outdated version of MySQL), you can have BOTH protected and default create_stamp file AND automatically update_stamp as follows:

If you have a table like

 CREATE TABLE `csv_status` ( `id` int(11) NOT NULL primary key AUTO_INCREMENT, `create_stamp` datetime not null, `update_stamp` timestamp default current_timestamp on update current_timestamp, `status` enum('happy','sad') not null default 'happy' ); 

Then you can define these triggers on it

 drop trigger if exists set_create_stamp ; create definer = CURRENT_USER trigger set_create_stamp BEFORE INSERT on csv_status for each row set NEW.create_stamp = now(); drop trigger if exists protect_create_stamp ; delimiter // create definer = CURRENT_USER trigger protect_create_stamp BEFORE UPDATE on csv_status for each row begin if NEW.create_stamp != OLD.create_stamp then set NEW.create_stamp = OLD.create_stamp; end if; end;// delimiter ;