Creating an immutable field in mysql

Question

I'd like to make a TIMESTAMP field DEFAULT CURRENT_TIMESTAMP, for 'creation time' purpose. But if someone or somehow something changes that TIMESTAMP, my data won't be consistent.

Is there a way I can ensure it won't change unless I delete the row and reinsert it, other than application level?

---

With the suggested answer provided, i could work around 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's the extended version.

I personally don't think that it's possible.

Anyway, there are a couple of things you can try:

1. Make sure that only your application can write on the database

2. Write a trigger like this (pseudocode!)

   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)
   

3. Or like this

   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'd personally go with the 3rd option, if possible. Anyway, the 2nd one is good too. I'd not rely on the 1st option unless necessary (eg: no access to trigger functionality)

More info here: reference

Answer 2

It's funny in a way that database apps don't offer this functionality as standard: not only for a "created" timestamp field, but for things like autoincrement id fields, and any miscellaneous values which you may want to set on creating a record and then never allow to be changed... wonder what the rationale is?