Posts tagged "constraints"

Postgres Constraint Naming Convention

Sometimes it's necessary to manually specify a constraint name, which should then ideally follow some sort of naming convention or pattern.

Postgres already has an implicit naming convention in place, which goes like this:

{tablename}_{columnname(s)}_{suffix}
  • pkey for primary key constraints

    • Single column

      create table foo (
        bar integer primary key
      );
                                  Table "public.foo"
       Column |  Type   | Collation | Nullable |             Default
      --------+---------+-----------+----------+---------------------------------
       bar    | integer |           | not null |
      Indexes:
          "foo_pkey" PRIMARY KEY, btree (bar)
    • Multiple columns

      create table foo (
        bar integer,
        baz integer,
        primary key (bar, bar)
      );
                      Table "public.foo"
       Column |  Type   | Collation | Nullable | Default
      --------+---------+-----------+----------+---------
       bar    | integer |           | not null |
       baz    | integer |           | not null |
      Indexes:
          "foo_pkey" PRIMARY KEY, btree (bar, baz)
  • key for unique constraints

    • Single column

      create table foo (
        bar integer unique
      );
                  Table "public.foo"
       Column |  Type   | Collation | Nullable | Default
      --------+---------+-----------+----------+---------
       bar    | integer |           |          |
      Indexes:
        "foo_bar_key" UNIQUE CONSTRAINT, btree (bar)
    • Multiple columns

      create table foo (
        bar integer,
        baz integer,
        unique (bar, baz)
      );
                      Table "public.foo"
       Column |  Type   | Collation | Nullable | Default
      --------+---------+-----------+----------+---------
       bar    | integer |           |          |
       baz    | integer |           |          |
      Indexes:
          "foo_bar_baz_key" UNIQUE CONSTRAINT, btree (bar, baz)
  • excl for exclusion constraints

    create table foo (
      bar text,
      baz text,
      exclude using gist (bar with =, baz with =)
    );
                 Table "public.foo"
     Column | Type | Collation | Nullable | Default
    --------+------+-----------+----------+---------
     bar    | text |           |          |
     baz    | text |           |          |
    Indexes:
        "foo_bar_baz_excl" EXCLUDE USING gist (bar WITH =, baz WITH =)
  • idx for indices

    Indices can not be created without manually specifying a name.

  • fkey for foreign key constraints

    • Single column

      create table foo (
        bar integer primary key
      );
      create table qux (
        bar integer references foo
      );
                      Table "public.qux"
       Column |  Type   | Collation | Nullable | Default
      --------+---------+-----------+----------+---------
       bar    | integer |           |          |
      Foreign-key constraints:
          "qux_bar_fkey" FOREIGN KEY (bar) REFERENCES foo(bar)
    • Multiple columns

      create table foo (
        bar integer,
        baz integer,
        primary key(bar, baz)
      );
      create table qux (
        bar integer,
        baz integer,
        foreign key(bar, baz) references foo (bar, baz)
      );
                      Table "public.qux"
       Column |  Type   | Collation | Nullable | Default
      --------+---------+-----------+----------+---------
       bar    | integer |           |          |
       baz    | integer |           |          |
      Foreign-key constraints:
          "qux_bar_fkey" FOREIGN KEY (bar, baz) REFERENCES foo(bar, baz)
  • check for check constraints

    • Single column

      create table foo (
        bar integer check (id > 10)
      );
                      Table "public.foo"
       Column |  Type   | Collation | Nullable | Default
      --------+---------+-----------+----------+---------
       bar    | integer |           |          |
      Check constraints:
          "foo_bar_check" CHECK (id > 10)
    • Multiple columns

      create table foo (
        bar integer,
        baz integer,
        check (bar = baz)
      );
                      Table "public.foo"
       Column |  Type   | Collation | Nullable | Default
      --------+---------+-----------+----------+---------
       bar    | integer |           |          |
       baz    | integer |           |          |
      Check constraints:
          "foo_check" CHECK (bar = baz)
  • seq for sequences

    create table foo (
      id serial
    );
                                Table "public.foo"
    Column |  Type   | Collation | Nullable |             Default
    --------+---------+-----------+----------+---------------------------------
     id     | integer |           | not null | nextval('foo_id_seq'::regclass)