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

Answer 3

Although 71's trigger solution is the general purpose approach, since it can be used for more complicated conditions, in your case where you just want to check for a constant value, you can stay closer to database logic and add a foreign key to a table that just contains that one allowed value in it, e.g.

create table tbl_checkconst (constraintvalue int primary key);
insert into tbl_checkconst values (71);

alter table yourtable 
add constraint fk_yourtable_constcheck 
foreign key (column1)
references tbl_chechconst (constraintvalue);

It will actually add some overhead (since it will need to add an index), but would express your constraint in database logic, and your constant usually has a meaning that is in this way designed into the database model (although it is just 1 value now), and you (and any user with the correct permissions) can easily add more allowed values by adding it to the tbl_checkconst-table without modifying your trigger code.

And another reason I added it is that I guess you are really actually looking for a foreign key: In one of your comments you said you are trying to create a "double foreign key to a reference table". If I understand that correctly, you might want to use a composite foreign key, since you are able to combine columns for a foreign key:

alter table yourtable 
add constraint fk_yourtable_col1col2 
foreign key (column1, column2)
references your_reference_table (refcolumn1, refcolumn2);

Answer 4

You would just set up a CHECK constraint in your table when you set it up. Something like this is all you need in most DBMSs

CREATE someTable 
(
someValue int(4) CHECK (someValue = 4) 
)

However, with MySQL, CHECK constraints don't behave the same as they do in other DBMSs. The situation is a little more tricky. The answer seems to be here: https://stackoverflow.com/a/14248038/5386243