Constant column value in MySQL table

Question

I have a table in MySQL. I'd like to set a column value for a table to be a constant integer. How can I do this?

Answer 1

Unfortunately MySQL does not support SQL check constraints. You can define them in your DDL query for compatibility reasons but they are just ignored. You can create BEFORE INSERT and BEFORE UPDATE triggers which either cause an error or set the field to its default value when the requirements of the data are not met.

So here you can find a way around through MYSQL TRIGGER.

Sample Table:

DROP TABLE IF EXISTS `constantvaluetable`;
CREATE TABLE `constantvaluetable` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `constValue` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB;

Trigger:

DROP TRIGGER IF EXISTS trigger_const_check;
delimiter //
CREATE TRIGGER trigger_const_check BEFORE INSERT ON constantvaluetable 
    FOR EACH ROW 
  BEGIN 
        IF NEW.constValue <> 71 THEN 
        SIGNAL SQLSTATE '45000' SET message_text ='Only allowed value is 71';
        END IF; 
  END //
delimiter ;

Test:

INSERT INTO constantvaluetable(constValue) VALUES(71);

INSERT INTO constantvaluetable(constValue) VALUES(66);

Result:

The first insert statement will succeed.

The second insert statement will fail. And the following error message will be shown:

[Err] 1644 - Only allowed value is 71

Note: Assuming your CONSTANT value is 71.

Answer 2

Do you really want to do this?

Would the following not suffice

Select Field1, field2, field3 , 5 as `ConstantField` from myTable