MySQL: set field default value to other column

Question

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 ); 

Answer 1

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;;