Обсуждение: how to rename an unnamed uniqueness constraint?

Hello,

sorry if I post this message a second time,
but I don't know whether I was succesful the
first time, and since actually it's quite
urgent that I solve the problem described below,
I forward this message (again or not).

----- Forwarded message from Oliver Kullmann <O.Kullmann@swansea.ac.uk> -----

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.


----- End forwarded message -----

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

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)
> )
> ;

Since this is the novice list, I will add that that is a round-about way
of declaring the table.

CREATE TABLE current_academic_year
(
    year_id        INT     PRIMARY KEY REFERENCES academic_years,
    active        BOOLEAN    NOT NULL UNIQUE
);
does the same and is easier to read.

Also, your capitalisation of table names won't be preserved, because you
don't double-quote them.

--
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

On Tue, Aug 16, 2005 at 11:11:23PM +0100, Oliver Elphick wrote:
> 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;
>

Thanks, that worked!

> TIP 3: Have you checked our extensive FAQ?
>                http://www.postgresql.org/docs/faq
>

aha, looks good. I've added it to my bookmarks.

Oliver

On Wed, Aug 17, 2005 at 06:52:08AM +1200, Andrej Ricnik-Bay wrote:
> In psql a
> \d+ <tablename>
> will give you the name of the actual constraint.
>
>
> Cheers,
> Tink
>

thanks!

BTW: why "d+ <tablename>" and not "d <tablename>" ?!
In the examples I tried the results where the same
(only d+ seems to add some spaces).