Better to use SERIAL PRIMARY KEY or GENERATED ALWAYS AS IDENTITY for primary key in PostgreSQL
Sep 22
Not sure which option is latest best practice? I read on on this tutorial that:
https://www.postgresqltutorial.com/postgresql-identity-column/
PostgreSQL version 10 introduced a new constraint GENERATED AS IDENTITY that allows you to automatically assign a unique number to a column.
The GENERATED AS IDENTITY constraint is the SQL standard-conforming variant of the good old SERIAL column.
In the example they use the identity as the primary key:
CREATE TABLE color (
color_id INT GENERATED ALWAYS AS IDENTITY,
color_name VARCHAR NOT NULL
);
When you reference this table for a FOREIGN KEY as per the below:
CREATE TABLE pallet (
id INT GENERATED ALWAYS AS IDENTITY,
color_1 REFERENCES color
color_2 REFERENCES color
);
Will it know that the identity is the primary key now?:
postgresql1 answer
Accepted answer · original discussion
Sep 22
Will it know that the identity is the primary key now?
No (and neither would a serial do that).
You need to define the primary key explicitly:
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?
It's recommended to use identity instead of serial.
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.
Finally, identity columns conform with the SQL standard, while serial is PostgreSQL dialect.
0 question comments
Use comments to ask for clarification. Post a solution as an answer.
No question comments on this page.