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









Fri, 07 May 2004 12:25:07 +0200
Gaetano Mendola <mendola@bigfoot.com>
Constraint not shown on \d  ?
> 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


Sat, 08 May 2004 20:01:33 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Constraint not shown on \d  ?
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




Sat, 08 May 2004 15:16:50 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: Constraint not shown on \d  ?