Protect column, disallow update, only allow insert if NULL in MySQL

Question

I want to protect existing dates in a date column from being overwritten. So disallow updates to the date column and only allow inserts if the existing field value is NULL (date column default is NULL). Are triggers the only way to accomplish this in MySQL? If so, would the trigger below work?

create trigger date_check
before insert, update on date
for each row
begin
if(date IS NOT NULL) then
 SIGNAL 'date already set'
end if ;
end ;

Background: I have a table with critical dates that was accidentally changed due to user error. I put some checks in the user interface to prevent this from happening again but want another layer of safety directly with the database if possible.

Answer 1

Yes, in MySQL triggers are the only way to do this. MySQL does not support constraints.

Your trigger is not exactly right. First, you have update on date, but this should be update on <table name>. Second, you are checking the date value used for the update. Perhaps you mean:

create trigger date_check_update
before update on <the table name goes here>
for each row
begin
    if (old.date IS NOT NULL) then
        SIGNAL 'date already set'
    end if ;
end;

An insert trigger on this condition doesn't make sense.

Answer 2

If anyone like me stumble upon this thread and is getting syntax error, it's because "When you try to raise errors via SIGNAL you need to specify the SQLSTATE which is the error code and for the user defined generic error codes its 45000 along with the message text MESSAGE_TEXT"

So the SIGNAL line should look like this.

signal SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Your custom error message';

See this answer for more details.

https://stackoverflow.com/a/42827275/4164651