SQL Database Design » 9. Default Values » 9.2 Derived Values

9.2 Derived Values

The SQL standard allows the generation of values dependent on other columns. Even though on-the-fly calculations are usually more efficient, it can sometimes be necessary to derive column values from other columns. The syntax is as follows:

CREATE TABLE Articles
(
  ...
  stock_items INTEGER      NOT NULL,
  list_price  DECIMAL(7,2) NOT NULL,
  stock_value GENERATED ALWAYS AS (stock_items * list_price),
  ...
)

Note, by standard SQL, only ALWAYS is possible with the GENERATED clause. On DBMSs that do not support the GENERATED clause, you might have to implement a trigger to reach equivalent functionality.

Last updated: 2010-08-04