Re: how to rename an unnamed uniqueness constraint?

On Tue, 2005-08-16 at 19:01 +0100, Oliver Kullmann wrote:
>
> I have a table created with
>
> CREATE TABLE Current_academic_year
> (
>     year_id        INT    REFERENCES Academic_years,
>     CONSTRAINT year_id CHECK(year_id IS NOT NULL),
>     active        BOOL    NOT NULL,
>
>     UNIQUE(year_id),
>     UNIQUE(active)
> )
> ;

> Now I need to drop the constraint "UNIQUE(active)".
> I tried variations of
>
> ALTER TABLE current_academic_year DROP CONSTRAINT "???"
>
> but I didn't succeed.
>
> I'm using version 7.4.8.
>
> Thanks in any case for your efforts!

\d current_academic_year

would show you that you have an index called
current_academic_year_active_key to enforce the unique constraint.

  junk=# DROP INDEX current_academic_year_active_key;
  ERROR:  cannot drop index current_academic_year_active_key because constraint current_academic_year_active_key on
tablecurrent_academic_year requires it 
  HINT:  You may drop constraint current_academic_year_active_key on table current_academic_year instead.

Therefore, what you need to do is:

ALTER TABLE current_academic_year DROP CONSTRAINT current_academic_year_active_key;

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html