MySQL: set field default value to other column
default, default-value, mysql
Solution
As documented under Data Type Default Values:
The `DEFAULT value` clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as `NOW()` or `CURRENT_DATE`. The exception is that you can specify `CURRENT_TIMESTAMP` as the default for a `TIMESTAMP` column. See Section 11.3.5, “Automatic Initialization and Updating for `TIMESTAMP`”.
Instead, you can define an insertion trigger:
CREATE TRIGGER foo BEFORE INSERT ON TSM_TRANSACTION_TBL FOR EACH ROW
IF NEW.REMAINING IS NULL THEN
SET NEW.REMAINING := NEW.MAX_VALUE - NEW.BOOKED_UNITS;
END IF;;
Problem
How to set default value for a field to other column in MySQL? I have done it in Oracle with virtual field, but I do not know how to do it in MySQL. This is my table: ``` CREATE TABLE TSM_TRANSACTION_TBL ( TRANS_ID INT primary key auto_increment, LOCATION_ID INT, TRANS_DATE DATE, RESOURCE_ID INT, TS_ID INT, MAX_VALUE INT, BOOKED_UNITS INT default 0, REMAINING INT default MAX_VALUE - BOOKED_UNITS, BOOKED INT not null, USER_ID INT, TRANS_TIME TIMESTAMP ); ```