Thread: Constraint not shown on \d ?

Hi all,
today I was tryng to delete an index but I had the following
error:

ERROR:  cannot drop index activation_code_code_key because constraint activation_code_code_key on 
table activation_code requires it
HINT:  You may drop constraint activation_code_code_key on table activation_code instead.


however this is what \d show:

# \d activation_code                                                Table "public.activation_code"       Column       |
       Type          |                                    Modifiers 
 


--------------------+-----------------------+---------------------------------------------------------------------------------
id_activation_code| integer               | not null default 
 
nextval('public.activation_code_id_activation_code_seq'::text) code               | character varying(64) | not null
id_code_pool      | integer               | not null
 
Indexes:    "activation_code_pkey" primary key, btree (id_activation_code)    "activation_code_code_key" unique, btree
(code,id_code_pool)
 
Check constraints:    "activation_code_code" CHECK (char_length(code::text) >= 5)
Foreign-key constraints:    "$1" FOREIGN KEY (id_code_pool) REFERENCES code_pool(id_code_pool) ON UPDATE CASCADE ON
DELETE
 
CASCADE



basically what is the difference between:

CREATE UNIQUE INDEX "activation_code_code_key" ON "public"."activation_code"
USING btree ("code", "id_code_pool");

or

ALTER TABLE activation_code ADD UNIQUE ( code, id_code_pool );

with \d command there is no difference but is different because the first command
create an index deleteable with a "drop index".




Regards
Gaetano Mendola

> CREATE UNIQUE INDEX "activation_code_code_key" ON 
> "public"."activation_code"
> USING btree ("code", "id_code_pool");
> 
> or
> 
> ALTER TABLE activation_code ADD UNIQUE ( code, id_code_pool );
> 
> with \d command there is no difference but is different because the 
> first command
> create an index deleteable with a "drop index".

Yes, you're right.  Basically the only difference is that the latter 
will make it a "constraint" that can only be dropped with DROP CONSTRAINT.

Also, if you wanted to create a unique non-btree index, partial index or 
expressional index, you'd have to use the former syntax

Chris

Christopher Kings-Lynne wrote:

>> CREATE UNIQUE INDEX "activation_code_code_key" ON 
>> "public"."activation_code"
>> USING btree ("code", "id_code_pool");
>>
>> or
>>
>> ALTER TABLE activation_code ADD UNIQUE ( code, id_code_pool );
>>
>> with \d command there is no difference but is different because the 
>> first command
>> create an index deleteable with a "drop index".
> 
> 
> Yes, you're right.  Basically the only difference is that the latter 
> will make it a "constraint" that can only be dropped with DROP CONSTRAINT.

but at the end why choose one ot other one, and basic all why

drop index idx_my_index cascade;

doesn't drop the constraint too?



Regards
Gaetano Mendola