Re: how to rename an unnamed uniqueness constraint? - Mailing list pgsql-novice

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/