Why write CONSTRAINT uq_users_email before UNIQUE (email) instead of leaving the constraint unnamed?

## What the clause is `CONSTRAINT <name>` is an optional prefix that may be attached to any constraint declaration, in either syntactic position: ```sql CREATE TABLE users ( id BIGINT NOT NULL, email VARCHAR(320) NOT NULL, CONSTRAINT pk_users PRIMARY KEY (id), CONSTRAINT uq_users_email UNIQUE (email) ); -- also legal inline email VARCHAR(320) CONSTRAINT uq_users_email UNIQUE ``` It does not affect what the constraint enforces or when. It gives the constraint a stable identifier in the catalog. ## What happens when you leave it out The constraint still exists — a name is not required. The engine synthesises one. What it synthesises is the problem: - The pattern differs between engines, so the same portable DDL yields different names on different products. - Collisions are resolved by appending digits, so the name can depend on the order in which objects were created. - Nothing in your source tree records the name, so it exists only in whichever database happened to run the DDL. That is fine right up to the first time you need to refer to the constraint, which is exactly when you are least able to shrug it off. ## Where the name is required Schema evolution addresses constraints by name: ```sql ALTER TABLE users DROP CONSTRAINT uq_users_email; ALTER TABLE users ADD CONSTRAINT uq_users_email_lower UNIQUE (email_normalised); ``` There is no portable "drop the unique constraint on this column" form. With an unnamed constraint, a migration must first query the catalog to discover the generated name, or a human must look it up interactively per environment. Both work; neither belongs in a repeatable deployment script. Hard-coding a generated name is worse still: it can be right in staging and wrong in production if the objects were created in a different order. ## The name is also an error message When an INSERT or UPDATE violates a constraint, engines report the constraint name. A well-chosen name turns an opaque failure into a diagnosis: ``` ERROR: duplicate key value violates unique constraint "uq_users_email" ERROR: new row violates check constraint "ck_orders_total_non_negative" ``` Application code often keys on this too: a write path that catches a unique violation and converts it into a friendly "that email is already registered" needs to distinguish *which* unique rule fired when a table has several. Matching on a name you chose is stable; matching on a generated one is a latent bug. ## Conventions worth adopting A convention is only useful if it is mechanical enough that two engineers produce the same name for the same constraint: - prefix by kind — `pk_`, `uq_`, `fk_`, `ck_` - then the table, then the columns or the rule: `fk_order_item_order`, `uq_roster_team_jersey`, `ck_orders_total_non_negative` - always include the table name, so the name is unambiguous when read in an error log or a migration file Two practical limits. Identifier length is capped, and the cap differs by engine — long table names plus long column lists can overflow it, so shorten the columns rather than dropping the table name. And identifiers written unquoted are case-folded by the engine, so treat names as lower-case and avoid quoted mixed-case names that then must be quoted everywhere. ## Scoping The standard treats a constraint name as a schema-level identifier, so a name should be unique within the schema rather than merely within the table. Engines differ in how strictly they enforce that, and some also place the backing object for a unique or primary key in the same namespace as indexes. Including the table name in every constraint name sidesteps all of it — you never have to know which rule your engine applies. ## Is it ever fine to skip? For a throwaway table in a scratch database, sure. For anything under migration control, name every constraint. The cost is a few characters at declaration time; the cost of not doing it is paid later, under time pressure, by someone reading an error message that names an object they cannot find in the repository.