preferIdentity

Diagnostic Category: lint/safety/preferIdentity

Since: vnext

Sources: - Inspired from: squawk/prefer-identity

Description

Prefer using IDENTITY columns over serial columns.

SERIAL types (serial, serial2, serial4, serial8, smallserial, bigserial) use sequences behind the scenes but with some limitations. IDENTITY columns provide better control over sequence behavior and are part of the SQL standard.

IDENTITY columns offer clearer ownership semantics - the sequence is directly tied to the column and will be automatically dropped when the column or table is dropped. They also provide better control through GENERATED ALWAYS (prevents manual inserts) or GENERATED BY DEFAULT options.

Examples

Invalid

create table users (
    id serial
);

code-block.sql:1:1 lint/safety/preferIdentity ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ! Prefer IDENTITY columns over SERIAL types.

  > 1 │ create table users (
      │ ^^^^^^^^^^^^^^^^^^^^
  > 2 │     id serial
  > 3 │ );
      │ ^^
    4 │ 

  i Column uses 'serial' type which has limitations compared to IDENTITY columns.

  i Use 'bigint GENERATED BY DEFAULT AS IDENTITY' or 'bigint GENERATED ALWAYS AS IDENTITY' instead.

create table users (
    id bigserial
);

code-block.sql:1:1 lint/safety/preferIdentity ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ! Prefer IDENTITY columns over SERIAL types.

  > 1 │ create table users (
      │ ^^^^^^^^^^^^^^^^^^^^
  > 2 │     id bigserial
  > 3 │ );
      │ ^^
    4 │ 

  i Column uses 'bigserial' type which has limitations compared to IDENTITY columns.

  i Use 'bigint GENERATED BY DEFAULT AS IDENTITY' or 'bigint GENERATED ALWAYS AS IDENTITY' instead.

Valid

create table users (
    id bigint generated by default as identity primary key
);

create table users (
    id bigint generated always as identity primary key
);

How to configure

{
  "linter": {
    "rules": {
      "safety": {
        "preferIdentity": "error"
      }
    }
  }
}