Search code examples
postgresqlunique-constraint

Remove unnamed constraint from PostgreSQL

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?

Solution

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