The naming convention to use for PostgreSQL constraints is the one PostgreSQL already uses: <table>_<columns>_<suffix>, with pkey, key, fkey, check and idx as the suffixes. Write those names out explicitly in your migrations rather than leaving them to the default, and keep every one under 63 bytes.

That sounds like a style choice, and most of it is. Three parts are not: index names share one namespace across the whole schema, a default name depends on what the database already contains, and a name longer than 63 bytes is cut without an error. Everything below was run on PostgreSQL 18.3 in a throwaway container, with the exact output where it matters.

What PostgreSQL names a constraint when you don’t

Create a table without naming anything and read pg_constraint back:

CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);
CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers,
  total numeric(12,2) NOT NULL CHECK (total >= 0),
  status text NOT NULL CHECK (status IN ('open', 'paid', 'shipped'))
);
CREATE INDEX ON orders (customer_id);

PostgreSQL picks customers_pkey, customers_email_key, orders_pkey, orders_customer_id_fkey, orders_total_check, orders_status_check and the index orders_customer_id_idx. On 18, each NOT NULL also appears as a named constraint, orders_total_not_null, because PostgreSQL 18 began storing NOT NULL in pg_constraint, which “allows names to be specified for NOT NULL constraint”. An expression index such as CREATE INDEX ON orders (lower(status)) is named after the function, orders_lower_idx.

The pattern is good. It puts the table first, so names sort together, and it says what kind of object each one is. The problem is the word “default”: the name you get depends on the database at that moment. Run ALTER TABLE customers ADD UNIQUE (email) a second time and PostgreSQL does not complain. It creates a second unique constraint, and a second index, called customers_email_key1. A migration that adds an unnamed constraint can produce _key on staging and _key1 on a production database where an old one survived, and the next migration that drops it by name only works on one of them.

Postgres constraint naming convention: which pattern should you use?

Pick one pattern and spell it out in every migration. Four patterns are common, and a database that has passed through more than one tool usually holds all of them:

The PostgreSQL pattern wins for one practical reason: any constraint anybody creates without a name, in a hotfix, a console session or a tool you do not control, lands in that pattern anyway. Choosing it means the exceptions look like everything else. Prisma already writes it, and in Django every UniqueConstraint and CheckConstraint in Meta.constraints takes a required name, which is where you can write it.

Whatever the framework generates for its own keys, two rules hold for the names you write by hand:

• Put the table name in every name. Check and foreign key names are unique per table, so n_positive can exist on two tables. Primary keys, unique constraints and indexes are backed by an index, and an index is a relation, so its name must be unique across the whole schema. Name a unique constraint email_unique on a second table and PostgreSQL answers relation "email_unique" already exists. The same happens with two indexes called idx_email.
• Name a CHECK after the rule, not the column. orders_total_check is fine while a column has one check. With two, orders_total_non_negative and orders_total_max tell you which one an error refers to.

What happens to a constraint name over 63 bytes?

PostgreSQL keeps the first 63 bytes. The manual states it plainly: “The system uses no more than NAMEDATALEN-1 bytes of an identifier; longer names can be written in commands, but they will be truncated.” It is 63 bytes, not characters, so a name with non-ASCII letters runs out sooner.

For its own default names PostgreSQL is careful. It shortens the table and column parts and keeps the suffix, so an unnamed unique constraint on the column below becomes subscription_billing_adjustme_customer_account_reference_nu_key, exactly 63 bytes. An explicit name gets no such care. It is cut at byte 63 wherever that falls, which on a long name is usually inside the suffix, and the only signal is a NOTICE:

CREATE TABLE subscription_billing_adjustments_v2 (
  id int PRIMARY KEY,
  customer_account_reference_number text
);
ALTER TABLE subscription_billing_adjustments_v2
  ADD CONSTRAINT subscription_billing_adjustments_v2_customer_account_reference_number_key
  UNIQUE (customer_account_reference_number);
-- NOTICE:  identifier "subscription_billing_adjustments_v2_customer_account_reference_number_key"
--          will be truncated to "subscription_billing_adjustments_v2_customer_account_reference_"

ALTER TABLE subscription_billing_adjustments_v2
  ADD CONSTRAINT subscription_billing_adjustments_v2_customer_account_reference_number_check
  CHECK (customer_account_reference_number <> '');
-- NOTICE:  identifier "subscription_billing_adjustments_v2_customer_account_reference_number_check"
--          will be truncated to "subscription_billing_adjustments_v2_customer_account_reference_"
-- ERROR:  constraint "subscription_billing_adjustments_v2_customer_account_reference_"
--         for relation "subscription_billing_adjustments_v2" already exists

Two different names became one, and the check was never created. An index named with the _idx version fails the same way, as relation ... already exists. When the cut falls a little later, inside the suffix but after the names have already diverged, nothing fails at all: a _key and a _fkey on a 61-byte prefix are both created, as names ending in _k and _f, which match nothing in your migration files. PostgreSQL applies the same cut when you look a name up, so DROP CONSTRAINT with the long name still works. What breaks is anything that compares the name it wrote with the name the catalogue holds, and anyone reading an error message that names a constraint ending in _reference_.

The fix is to shorten the middle, not the end. Abbreviate the table or column part until the whole name fits, or shorten it and add a short hash, the way Django builds its generated names, so two long names cannot end up as one.

Renaming a table does not rename its constraints

ALTER TABLE orders RENAME TO purchases renames the table and nothing else. Every constraint keeps its old name, and the next error reads like a contradiction:

ERROR:  new row for relation "purchases" violates check constraint "orders_total_check"

Rename them in the same migration. ALTER TABLE purchases RENAME CONSTRAINT orders_total_check TO purchases_total_check works for every kind of constraint, and for a primary key or unique constraint it renames the index behind it too. Plain indexes and the identity sequence keep their old names as well: rename them with ALTER INDEX orders_customer_id_idx RENAME TO purchases_customer_id_idx and ALTER SEQUENCE orders_id_seq RENAME TO purchases_id_seq.

To find the names that have already drifted, list every constraint whose name does not start with its own table’s name:

SELECT c.conrelid::regclass AS table_name, c.conname
FROM pg_constraint c
JOIN pg_class t ON t.oid = c.conrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE n.nspname = 'public'
  AND c.contype IN ('p', 'u', 'f', 'c')
  AND c.conname NOT LIKE t.relname || '\_%';

Run against the tables above after the rename, it returned four rows, all on purchases: orders_pkey, orders_customer_id_fkey, orders_total_check and orders_status_check. On PostgreSQL 18, add 'n' to the list to include the NOT NULL names, which go stale the same way. The query reads only constraints in public, so plain indexes need a look of their own. It also lists names that are not drift: every fk_rails_ key in a database built by Rails, and PostgreSQL’s own defaults on a table long enough that the table part was shortened. Read the list before renaming anything.

How Schemity names constraints and indexes

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. When you connect to PostgreSQL, the constraints are read with the names the database holds, whichever tool wrote them, so a mixed database shows its mixed conventions rather than a tidied copy.

When you design the next one, a key, constraint or index you draw is named in the PostgreSQL pattern: orders_pkey, orders_customer_id_fkey, customers_email_key, orders_customer_id_idx, and for a check, the table, the rule name you type and _check, such as orders_total_non_negative_check. A composite unique constraint is named the same way, after all of its columns. When a name would pass 63 bytes, Schemity cuts the table and column part from its end, adds a short hash of the full name, and keeps the suffix, so the long example above becomes subscription_billing_adjustments_v2_customer_accoun_1jp0itp_key and its check subscription_billing_adjustments_v2_customer_accou_giumie_check, both exactly 63 bytes. The name in the diagram, the name in the planned SQL and the name in the database are the same string, and two names that agree up to the cut stay apart.

The constraint names are fitted, but a table or column name is whatever you type, and PostgreSQL cuts those at 63 bytes too. The lint rule identifier-too-long reports one before the migration runs, saying the name “is longer than PostgreSQL keeps”, with its byte count. On MySQL, where the limit is 64 characters, and SQL Server, at 128, the same rule says the statement that creates it fails, because those databases refuse the name instead of cutting it.

The convention, in one list

• Pattern: <table>_<columns>_<suffix>, with pkey, key, fkey, check, idx, the same as PostgreSQL’s defaults.
• Name every constraint explicitly in migrations, so every database gets the same name.
• Put the table name first in every name: index-backed names are unique across the schema.
• Name a CHECK after its rule once a column has more than one.
• Stay under 63 bytes; shorten the middle or add a hash, never let PostgreSQL cut the suffix.
• Rename constraints and indexes in the same migration that renames the table, and run the drift query once on an old database.

The same kind of decision for unique rules, constraint or index, is in unique constraint vs unique index in Postgres. Where a check’s name ends up mattering most, in the allowed values of a column, is covered in enum vs check constraint vs lookup table.