增强表结构-Identity-Column

FortuneXu 2026-01-04

344

摘要：在本教程中，您将学习如何使用 PostgreSQL 标识列，该列使用隐式序列自动生成唯一整数。

`PostgreSQL` 身份列入门

在 PostgreSQL 中，身份列是一项特殊功能，它通过使用隐式序列自动生成唯一整数来带来便利。

要定义标识列，请使用以下两个约束之一：

GENERATED ALWAYS AS IDENTITY ：这可确保列始终使用生成的整数。如果列具有此约束，则不能在 insert 或 update 语句中提供显式值，除非在 INSERT 语句中使用 OVERRIDING SYSTEM VALUE 。
GENERATED BY DEFAULT AS IDENTITY 此约束与上述类似，允许您提供用于插入的显式整数，从而提供灵活性。

通常使用标识列作为表的主键列，因为它会自动生成唯一的整数，从而简化主键的管理。

以下语句演示如何将列定义为标识列作为主键列：

CREATE TABLE table_name (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  column1 data_type,
  ...
);

除了 INT 数据类型外，还可以使用 SMALLINT 和 BIGINT 数据类型。

由于 id 列使用 GENERATED AS ALWAYS IDENTITY 约束，因此在将行插入表时无法提供 id。

以下语句将失败：

INSERT INTO
  table_name (id, column1)
VALUES
  (1, value1);

但是，如果您有充分的理由使用显式 id 值，则 INSERT 语句中的 OVERRIDING SYSTEM VALUE 子句为您提供了控制权：

INSERT INTO
  table_name (id, column1) 
OVERRIDING SYSTEM VALUE
VALUES
  (1, value1);

在此语法中，OVERRIDING SYSTEM VALUE 子句允许您将值 1 显式插入 id 列。

下面显示如何定义使用 GENERATED BY DEFAULT AS IDENTITY IDENTITY 约束的标识列：

CREATE TABLE table_name (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    column1 data_type,
    ...
);

在此表中，您可以让序列生成唯一的整数或在 INSERT 语句中显式提供整数：

INSERT INTO table_name(id, column1)
VALUES(1, value1);

使用 `PostgreSQL` 身份列作为主键列

首先，创建一个存储销售订单标题的表 sales_orders ：

CREATE TABLE sales_orders(
    order_no INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    status VARCHAR(25) NOT NULL,
    requested_date DATE NOT NULL,
    created_date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

sales_orders 表的 id 列是标识列。

其次，在 sales_orders 表中插入两行：

INSERT INTO
  sales_orders (customer_name, status, requested_date)
VALUES
  ('John Doe', 'Pending', '2024-12-15'),
  ('Jane Doe', 'Completed', '2024-12-20') 
RETURNING order_no;

输出：

 order_no
----------
        1
        2

由于 order_no 是标识列，因此 PostgreSQL 会自动生成唯一的整数。

sales_orders 表的 id 列使用 GENERATED ALWAYS AS IDENTITY 约束。因此，它不会接受隐式值。

第三，尝试在具有隐式 id 的表中插入新行：

INSERT INTO sales_orders (order_no , customer_name, status, requested_date)
VALUES(3, 'Adam Smith', 'Pending', '2024-12-17');

错误：

Column "order_no" is an identity column defined as GENERATED ALWAYS.

要将值插入 GENERATED ALWAYS AS IDENTITY IDENTITY 列，可以在 insert 语句中使用 OVERRIDING SYSTEM VALUE 子句：

INSERT INTO sales_orders (order_no , customer_name, status, requested_date)
OVERRIDING SYSTEM VALUE
VALUES(3, 'Adam Smith', 'Pending', '2024-12-17')
RETURNING order_no;

输出：

 order_no
----------
        3

使用 `PostgreSQL` 身份列作为非主键列

首先，创建一个名为 sales_order_items 的新表，用于存储销售订单的项目：

CREATE TABLE sales_order_items (
  order_no INT,
  item_no INT GENERATED BY DEFAULT AS IDENTITY UNIQUE,
  product_name VARCHAR(255) NOT NULL,
  quantity INT NOT NULL CHECK (quantity > 0),
  price DEC(15, 2) NOT NULL CHECK (price > 0),
  PRIMARY KEY (order_no, item_no),
  FOREIGN KEY (order_no) REFERENCES sales_orders (order_no) ON DELETE CASCADE
);

其次，在订单 #1 中添加两个项目：

INSERT INTO
  sales_order_items (order_no, product_name, quantity, price)
VALUES
  (1, 'iPhone 16 Pro', 5, 1299.99),
  (1, 'Screen Protector', 5, 9.99) 
RETURNING order_no, item_no;

输出：

 order_no | item_no
----------+---------
        1 |       1
        1 |       2

第三，将新项目添加到销售订单中，但明确提供 item_no 值：

INSERT INTO
  sales_order_items (order_no, item_no, product_name, quantity, price)
VALUES
  (1, 3, 'Charger', 5, 29.99)
RETURNING order_no, item_no;

输出：

 order_no | item_no
----------+---------
        1 |       3

由于 item_no 列使用 GENERATED BY DEFAULT AS IDENTITY IDENTITY 约束，因此我们可以显式地在其中插入一个整数。

隐式序列

当您为表创建标识列时，PostgreSQL 会自动生成隐式序列并将其与该列相关联。

序列将具有以下命名约定：

<table_name>_<column_name>_seq

例如，PostgreSQL 为 sales_orders 表的 order_no 列创建以下序列：

sales_orders_order_no_seq

同样，它为 sales_order_items 表的 item_no 列创建以下序列：

sales_order_items_item_no_seq

PostgreSQL 允许您在定义身份列时配置序列。

例如，以下内容创建一个表 sales_order_item_schedules ，用于存储销售订单中每个行项的交货日期和数量的计划。

CREATE TABLE sales_order_item_schedules (
  item_no INT,
  schedule_no INT GENERATED BY DEFAULT AS IDENTITY (INCREMENT 10 START 10),
  delivery_qty INT NOT NULL,
  delivered_on DATE NOT NULL,
  PRIMARY KEY (item_no, schedule_no),
  FOREIGN KEY (item_no)  
      REFERENCES sales_order_items (item_no) 
      ON DELETE CASCADE
);

schedule_no 列是标识列。此外，我们配置基础序列，以便每个销售订单行项的数字从 10 开始，递增 10。

以下语句在 sales_order_item_schedules 表中插入三行：

INSERT INTO
  sales_order_item_schedules (item_no, delivery_qty, delivered_on)
VALUES
  (1, 1, '2024-12-16'),
  (1, 2, '2024-12-17'),
  (1, 2, '2024-12-17') 
RETURNING item_no, schedule_no ;

输出：

 item_no | schedule_no
---------+-------------
       1 |          10
       1 |          20
       1 |          30

输出指示 schedule_no 列中的值从 10 开始，并每行递增。

将现有列配置为标识列

要将列更改为标识列，可以使用 ALTER TABLE 语句：

ALTER TABLE table_name
ALTER COLUMN column_name
ADD GENERATED { ALWAYS | BY DEFAULT } 
AS IDENTITY 
[sequence_option];

column_name 必须具有 NOT NULL 约束，否则会遇到错误。

让我们举个例子：

首先，创建一个表 taxes 来存储销售税：

CREATE TABLE taxes (
   id INT PRIMARY KEY, 
   rate DECIMAL(5, 2) NOT NULL
);

其次，将 id 列设置为标识列：

ALTER TABLE taxes
ALTER COLUMN id 
ADD GENERATED ALWAYS AS IDENTITY;

第三，在 taxes 中插入一些行：

INSERT INTO
  taxes (rate)
values
  (0.05),
  (0.08) 
RETURNING *;

输出：

 id | rate
----+------
  1 | 0.05
  2 | 0.08

设置标识列特征

PostgreSQL 允许您配置身份列的特征：

在 GENERATED ALWAYS 和 GENERATED BY DEFAULT 之间切换。
更改隐式序列的参数。

语法如下：

ALTER TABLE table_name
ALTER COLUMN column_name
SET GENERATED { ALWAYS | BY DEFAULT}
[SET sequence_option]

例如：

首先，将税表的 id 列从 GENERATED ALWAYS 更改为 GENERATED BY DEFAULT

ALTER TABLE taxes
ALTER COLUMN id
SET GENERATED BY DEFAULT;

其次，在税表的 id 列中插入一个显式整数值：

INSERT INTO taxes(id, rate)
VALUES(3, 0.1)
RETURNING *;

输出：

 id | rate
----+------
  3 | 0.10

第三，将增量值设置为 5，而不是默认使用 1：

ALTER TABLE taxes
ALTER COLUMN id
SET INCREMENT BY 5;

最后，在 taxes 表中插入一行新行：

INSERT INTO taxes(rate)
VALUES(0.12)
RETURNING *;

id 值是 7，而不是 8 （3 + 5），因为序列没有生成数字 3 。它返回下一个序列值 7 （ 2 + 5 ）。

删除 `GENERATED ALWAYS`（或默认情况下）作为 `IDENTITY` 约束

要从列中删除 GENERATED ALWAYS（或 BY DEFAULT）AS IDENTITY 约束，请使用 ALTER TABLE DROP IDENTITY 语句：

ALTER TABLE table_name
ALTER COLUMN column_name
DROP IDENTITY [ IF EXISTS ];

IF EXISTS 子句仅在标识约束存在时才有条件地删除标识约束。

例如，以下语句从 taxes 表的 id 列中删除 GENERATED ALWAYS（或 BY DEFAULTAS) IDENTITY 约束：

ALTER TABLE taxes
ALTER COLUMN id
DROP IDENTITY IF EXISTS;

总结

使用标识列使用隐式序列生成唯一整数。
使用 GENERATED AWAYS AS IDENTITY 或 GENERATED BY DEFAULT AS DEFAULT AS DEFAULT 约束设置标识列。

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

PostgreSQL-Tutorial（共104篇）

PostgreSQL-Tutorial系统的学习PostgreSQL实践资料

增强表结构-Identity-Column

PostgreSQL 身份列入门

使用 PostgreSQL 身份列作为主键列

使用 PostgreSQL 身份列作为非主键列