CODEWITHSUNDEEP
mysqldefaultdefault-value
BMW
BMW

Reputation: 630

MySQL: set field default value to other column

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

Upvotes: 41

Views: 59266

Answers (4)

Moatasem Alhumsi Alrefaai
Moatasem Alhumsi Alrefaai

Reputation: 1

After srearching.. i found some solutions.

first solution: using default

REMAINING    INT default (MAX_VALUE - BOOKED_UNITS)

this solution will only give value if you didn't give a value when insert.

second solution: using Trigger

SQL auto-create a value column

third solution: using GENERATED ALWAYS AS

number1 INT, number2 INT, result INT GENERATED ALWAYS AS(number2 - number1),

this solution will always fill it for you after insert

Upvotes: -1

John C
John C

Reputation: 1978

As of MySQL 8.0.13, you can reference another column as the default value of a column.

An expression default value for one column can refer to other table columns, with the exception that references to generated columns or columns with expression default values must be to columns that occur earlier in the table definition. That is, expression default values cannot contain forward references to generated columns or columns with expression default values.

CREATE TABLE table1 (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(250) NOT NULL,
  name_url varchar(250) NOT NULL DEFAULT (`name`),

  created datetime NOT NULL DEFAULT (UTC_TIMESTAMP()),
  modified datetime NOT NULL DEFAULT (UTC_TIMESTAMP()),

  PRIMARY KEY (id),
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci;

Upvotes: 14

eggyal
eggyal

Reputation: 125865

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

Upvotes: 31

Darkside
Darkside

Reputation: 201

The 'NEW' is unacceptable for AFTER insert triggers. You should do the 'field update' by a BEFORE insert trigger. So, 

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

Upvotes: 20

Related Questions