postgresql - 最好使用 SERIAL PRIMARY KEY 或 GENERATED ALWAYS AS IDENTITY 作为 PostgreSQL 中的主键

Question

不确定哪个选项是最新的最佳实践？我继续阅读本教程:
https://www.postgresqltutorial.com/postgresql-identity-column/

PostgreSQL version 10 introduced a new constraint GENERATED ASIDENTITY that allows you to automatically assign a unique number to acolumn.

The GENERATED AS IDENTITY constraint is the SQL standard-conformingvariant of the good old SERIAL column.

在示例中，他们使用身份作为主键:

CREATE TABLE color (
    color_id INT GENERATED ALWAYS AS IDENTITY,
    color_name VARCHAR NOT NULL
);

当您按照以下方式引用此表获取外键时:

CREATE TABLE pallet (
    id INT GENERATED ALWAYS AS IDENTITY,
    color_1 REFERENCES color
    color_2 REFERENCES color
);

它现在会知道身份是主键吗？:

Answer 1

Will it know that the identity is the primary key now?

不(而且 serial 也不会这样做)。
您需要明确定义主键:

CREATE TABLE color (
    color_id INT  primary key GENERATED ALWAYS AS IDENTITY,
    color_name VARCHAR NOT NULL
);

Not sure which option is latest best practice?

推荐使用 identity而不是 serial .
Quote from the Postgres Wiki

For new applications, identity columns should be used instead.

Why not serial?
The serial types have some weird behaviors that make schema, dependency, and permission management unnecessarily cumbersome.

最后，标识列符合 SQL 标准，而 serial是 PostgreSQL 方言。