How to make sure that the column value is always added in Uppercase?

Question

I have an existing column host and want to make sure that the column value host is always added in Uppercase.

I am using Alter Table command as below:

ALTER TABLE `mydb`.`myTable` 
CHANGE COLUMN `host` `host`
VARCHAR(255) GENERATED ALWAYS AS (UPPER()) STORED;

This is causing an error and I am not able to alter the column.

Operation failed: There was an error while applying the SQL script to the database. ERROR 1582: Incorrect parameter count in the call to native function 'UPPER' SQL Statement: ALTER TABLE mydb.myTable CHANGE COLUMN host host VARCHAR(255) GENERATED ALWAYS AS (UPPER()) VIRTUAL

I want to make sure that moving forward the value inserted in host is always in uppercase. I want to achieve this by updating the value in uppercase at runtime. I don't want to add any constraint as it will cause an error when the host value is entered in lowercase.

Answer 1

It is not entirely clear what you are trying to do.

If you want to set a constraint on the table that allows only upper case letters in host, use a check constraint:

alter table mytable
    add constraint chk_host_upper
    check (host rlike '^[A-Z]*$')
;

This prevents lower case character to be written to the column; an attempt to do so results in a runtime error.

If, on the other hand, you want to create a new column, that returns the upper case value of host, regardless of the actual column code:

alter table mytable
     add column host_upper varchar(50)  -- same as the original column
     as (upper(host))
;

With this set up at hand, you can query the new column host_upper when you want the upper case values.

---

Finally: if you want to convert input value to upper case on the fly on inserts, then you need a trigger:

delimiter //

create trigger mytrigger
before insert on mytable
for each row
begin
    set new.host = upper(new.host);
end
//

delimiter ;