SQL Reference - GENERATED

GENERATED

GENERATED is a clause in SQL used with identity columns. Its purpose is to automatically generate a unique value for a particular column, typically a primary key, whenever a new row is added to the table. It simplifies the handling of unique identifiers within tables. There are two types, namely GENERATED ALWAYS and GENERATED BY DEFAULT, which determine when and how the database will generate and assign the new value.

Example

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    product_name VARCHAR(100),
    quantity INT,
    price DECIMAL(10,2),
    total_price DECIMAL(10,2) AS (quantity * price),
PRIMARY KEY(order_id)
);

INSERT INTO orders (product_name, quantity, price)
VALUES('Laptop', 5, 1000.00);

SELECT * FROM orders;

Output

+----------+--------------+----------+-------+-------------+
| order_id | product_name | quantity | price | total_price |
+----------+--------------+----------+-------+-------------+
|        1 | Laptop       |        5 | 1000.00 |    5000.00 |
+----------+--------------+----------+-------+-------------+

Explanation

In the example, the total_price column is a generated column that automatically calculates and stores the total price of each order by multiplying the quantity and price. When a new record is inserted into the orders table, the value for the total_price column is automatically calculated and stored in the total_price column.

Example

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name varchar(50),
    last_name varchar(50),
    full_name varchar(101) GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe');
SELECT * FROM employees;

Output

 id | first_name | last_name | full_name
----+------------+-----------+-----------
  1 | John       | Doe       | John Doe

Explanation

The GENERATED ALWAYS AS keyword is used to automatically generate a value for a specific column based on the values in other columns. In this case, the full_name column is automatically generated as a combination of the first_name and last_name columns. The STORED keyword means the generated values are physically stored in the table.

Example

CREATE TABLE employees (
    id number,
    name varchar(100),
    dob date,
    age as (months_between(sysdate, dob)/12)
);

Output

Table EMPLOYEES created.

Explanation

In the SQL code snippet, a new table named employees is created. This table contains three explicit columns id, name, dob, and a calculated column age. The GENERATED ALWAYS syntax used for the age column means it always appears with the value calculated from the expression (months_between(sysdate, dob)/12). This means, age is calculated automatically based on the date of birth (dob) and the current date (sysdate), and the value cannot be manually set or altered.