Question 46425 in Debian Question posted in
The oficial documentation can be found here.

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

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found the solution.

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


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search