How do you drop a UNIQUE constraint that was declared inline in CREATE TABLE with no name?

## DROP CONSTRAINT is name-addressed ```sql ALTER TABLE <table> DROP CONSTRAINT <constraint name> [ CASCADE | RESTRICT ]; ``` The statement takes a name and nothing else. There is no portable syntax for "drop whatever unique constraint covers `(tenant_id, external_ref)`" or "drop the foreign key pointing at `customers`". If you do not know the name, you cannot write the statement. ## Where the name comes from When a constraint is declared with an explicit `CONSTRAINT` clause, the name is yours and is stable everywhere the schema is created: ```sql ALTER TABLE orders ADD CONSTRAINT uq_orders_external_ref UNIQUE (external_ref); ALTER TABLE orders DROP CONSTRAINT uq_orders_external_ref; ``` When it is declared inline without one — `external_ref VARCHAR(64) UNIQUE` in `CREATE TABLE` — the constraint still exists, but the engine generates the name. Generated names follow engine-specific conventions and can vary with creation order, collisions, and the engine version. That is what makes an unnamed constraint awkward: the drop statement is not knowable from the schema source, only from the live catalog of the particular database. ## Finding it ```sql SELECT constraint_name, constraint_type FROM information_schema.table_constraints WHERE table_schema = 'public' AND table_name = 'orders'; ``` `information_schema.constraint_column_usage` (and `key_column_usage`) tells you which columns each constraint covers, which is how you identify the one you actually mean when several exist. Engines expose native catalogs with more detail, and most clients have a shortcut to show a table's definition. The operational hazard: doing this by hand is fine, but *baking the discovered name into a migration* assumes every environment generated the same name. When that assumption fails, the migration errors in production. The robust options are to make the migration look the name up dynamically, or — far better — to have named the constraint in the first place so this problem never exists. ## Adding constraints later The mirror statement is `ADD CONSTRAINT`, and it accepts the same constraint kinds that a table definition does: ```sql ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY (order_id); ALTER TABLE orders ADD CONSTRAINT uq_orders_external_ref UNIQUE (external_ref); ALTER TABLE orders ADD CONSTRAINT ck_orders_qty CHECK (qty > 0); ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (customer_id); ``` Every one of these is checked against the rows already in the table, and the statement is rejected if any row violates it — which is the same data-versus-schema tension seen in `SET NOT NULL`. Naming each one with a convention (`pk_`, `uq_`, `ck_`, `fk_` plus table and columns) makes the reverse statement writable from the schema source alone. ## NOT NULL is usually not a named constraint A frequent dead end: you try `ALTER TABLE t DROP CONSTRAINT c_not_null` and no such object exists. In most engines `NOT NULL` is a column attribute rather than a catalogued table constraint, so it is removed through the column: ```sql ALTER TABLE orders ALTER COLUMN shipped_at DROP NOT NULL; -- PostgreSQL ``` The exception is a `CHECK (col IS NOT NULL)` you wrote yourself, which *is* a named constraint and does drop by name. ## Engine differences worth knowing - **PostgreSQL** implements `ADD CONSTRAINT` / `DROP CONSTRAINT` for all constraint kinds, with `CASCADE`/`RESTRICT` on the drop (`CASCADE` matters when another constraint depends on the one being dropped — a foreign key referencing a unique key, for instance). - **MySQL** historically required object-specific forms: `DROP FOREIGN KEY <name>`, `DROP INDEX <name>` for a unique key, `DROP CHECK <name>`. Newer versions accept a generic `DROP CONSTRAINT`, but code that must run on older servers uses the specific forms. - **SQLite** does not support dropping a constraint at all; its `ALTER TABLE` covers only rename, add column, drop column. Removing a constraint means creating a new table with the desired definition, copying the rows, dropping the original and renaming — the classic table-rebuild. ## The takeaway Name every constraint you declare. It costs one clause at creation time and it is the difference between a reversible migration and an archaeology exercise against a production catalog.