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

Question

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

Perhaps the constraint "serveurs_domain" exists on another table. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Oct 21, 2022 at 16:24
The constraint name in your text "serveurs_abbr" does not match the constraint name in your SQL code serveurs_domain. — Adrian Klaver
– Adrian Klaver, Commented Oct 21, 2022 at 16:27
To find the table to which the existing constraint belongs to: select * from information_schema.constraint_table_usage where constraint_name = 'serveurs_domain'; — user330315
– user330315, Commented Oct 21, 2022 at 16:29

Adrian Klaver · Accepted Answer · 2022-10-21 16:46:28Z

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.

Karam · Accepted Answer · 2022-10-24 09:06:43Z

2

I found the solution.

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

answered Oct 24, 2022 at 9:06

Karam

315 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related