How do identity columns, sequences and generated columns work?
How do identity columns, sequences and generated columns work?
They let the database produce values for you: identity columns and sequences generate surrogate keys; generated columns compute a value from other columns.
| Engine | Syntax |
|---|---|
| SQL standard, PostgreSQL 10+, Oracle 12c+ | id BIGINT GENERATED ALWAYS AS IDENTITY (or BY DEFAULT) |
| SQL Server | id BIGINT IDENTITY(1,1) |
| MySQL / MariaDB | id BIGINT AUTO_INCREMENT |
| PostgreSQL (legacy) | SERIAL / BIGSERIAL, a shorthand for a sequence default |
CREATE TABLE tickets (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title VARCHAR(200) NOT NULL
);
INSERT INTO tickets (title) VALUES ('Printer is on fire') RETURNING id; -- PostgreSQL
ALWAYS rejects explicit values (unless overridden); BY DEFAULT accepts them.
Fetching the new id: PostgreSQL / SQLite RETURNING id, SQL Server OUTPUT INSERTED.id, MySQL LAST_INSERT_ID(), Oracle RETURNING id INTO :v.
A sequence is a separate counter object, shared across tables or sessions.
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.