Thread: 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).