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';