Thread: how to rename an unnamed uniqueness constraint?

Hello,

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!

Oliver

P.S. The "documentation" regarding the e-mail lists is
really the most arcane I've ever seen. I have no idea how
I'm supposed to post to a list, and/or to receive mails.
Of course I tried

http://mail.postgresql.org/mj/mj_wwwusr/domain=postgresql.org

and the help pages etc.: I always get "unsuccessful" back,
and the e-mails I get from "majordomo" just tell me that I
was unsuccessful (with registration).
And then there is "Sign in", "Sign out" ???
Just one paragraph about the idea how the average *novice*
should post a question and read the answer would be quite
useful.

Oliver Kullmann wrote:
> Hello,
>
> 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 "???"

You didn't name your constraints, so PostgreSQL named them for you. They are
probably called $1 and $2. Do "\d Current_academic_year" to see the
constraints and their names. You should see something like:
   "$1" unique, btree (year_id)
   "$2" unique, btree (active)

Once you know the name of the constraint, you can drop it:

ALTER TABLE current_academic_year DROP CONSTRAINT "$2";

Note that you need to put quotes around the automatically generated
constraint names, because they are not valid identifiers.



--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/