Remove unnamed constraint from PostgreSQL

Question

In PostgreSQL, I have the following table definition

create table file(
    file_id int generated by default as identity primary key,
    file_name text UNIQUE not null
);

My question is: how do I remove the unique constraint on file_name?

Answer 1

The default naming strategy that Postgres uses is tablename_columnname_key for such a constraint. For CHECK constraints the default name is tablename_columnname_check.

In your case the name would be file_file_name_key.

So you can use

alter table file drop constraint file_file_name_key;

If you don't want to rely on the default naming strategy, you can use the following query to retrieve the name:

select constraint_name
from information_schema.key_column_usage
where table_name = 'file'
  and table_schema = 'public'
  and column_name = 'file_name';

Answer 2

You'll have to query the metadata (pg_constraint, pg_index, pg_attribute) to find out the name of the constraint that is implemented by the unique index on your column.

PostgreSQL uses an internal logic to generate the name automatically (see the other answer), but it's fragile to rely on that: if there already is a constraint with that name, PostgreSQL will disambiguate by appending a number.

Answer 3

There is always name for any constraint - it's just if you don't specify Postgres or ORM (such as Hinernate) would generate one automatically.

If you use pgAdmin, you can just click on the table and it would show in description the list of constraints and their names:

And for the example above I just needed to run:

ALTER Table word_pairs drop constraint word_pairs_foreign_word_key;

If you don't use GUI you can find out constraint name like this:

SELECT tc.constraint_name FROM information_schema.table_constraints 
AS tc WHERE tc.constraint_type='UNIQUE' 
AND tc.table_name='word_pairs';

(this is a simplified adaptation of this answer)

Answer 4

In psql terminal, you can get the details of a table using the command:

\d your_table_name

and underneath the table description is a list of Indexes containing the name of all constraints in double quotes, the constraint itself (in uppercase) and the column to which it was applied (in parentheses).