PostgreSQL - Cannot drop unique constraint because it doesn't exist BUT it does exists-CodePudding

How is it possible that when I try to add a unique constraint named "serveurs_domain" it says I can't because the constraint already exists. BUT, when I try to drop the constraint "serveurs_abbr" it says it doesn't exist.

I'm confused ???

SQL :

ALTER TABLE serveurs
DROP CONSTRAINT serveurs_domain;

ERROR: constraint « serveurs_domain » of relation « serveurs » does not exist

ALTER TABLE serveurs
ADD CONSTRAINT serveurs_domain UNIQUE (domain);

ERROR: constraint « serveurs_domain » already exists

PgSQL Version :

select version();

PostgreSQL 13.8 (Debian 13.8-0 deb11u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

Screenshot

CodePudding user response：

The error correctly translated would have shown you the issue:

ERROR: relation "serveurs_domain" already exists

create table u_1 (id int, CONSTRAINT test_c  UNIQUE (id));

create table u_2 (id int)
alter table u_2 add constraint test_c unique(id);
ERROR:  relation "test_c" already exists


\d test_c
        Index "public.test_c"
 Column |  Type   | Key? | Definition 
-------- --------- ------ ------------
 id     | integer | yes  | id
unique, btree, for table "public.u_1

From pg_class:

The catalog pg_class catalogs tables and most everything else that has columns or is otherwise similar to a table. This includes indexes (but see also pg_index), sequences (but see also pg_sequence), views, materialized views, composite types, and TOAST tables; see relkind. Below, when we mean all of these kinds of objects we speak of “relations”. Not all columns are meaningful for all relation types.

So you are trying to reuse an existing index(relation) name and that won't work. FYI, it is a good habit to do:

ALTER TABLE serveurs
DROP CONSTRAINT IF EXISTS serveurs_domain;

In which case a NOTICE not a ERROR is thrown.

CodePudding user response：

I found the solution.

I should use DROP INDEX serveurs_domain; instead of ALTER TABLE ...