Re: alter table ad primary key

Alvaro Herrera wrote:

>En Fri, 23 Aug 2002 13:20:05 -0400
>Dmitry Tkach <dmitry@openratings.com> escribió:
>
>>Christoph Dalitz wrote:
>>
>>>Hello,
>>>
>>>trying "alter table buecher add primary key (isbn);"
>>>gives the error "ALTER TABLE / ADD CONSTRAINT is not implemented"
>>>with PG 7.1.
>>>
>>>Does anybody know whether this works with a newer PG version?
>>>
>
>Yes, it's supported since 7.2 I think.
>

Yeah, but only if it is already defined as 'not null' - otherwise, it
complains and aborts :-(

>
>
>>You don't really need all this...
>>
>>just:
>>
>>create unique index buecher_isbn_pkey on buecher(isbn);
>>update pg_attribute set attnotnull='t' from pg_class where attrelid=oid and relname='buecher' and attname='isbn';
>>
>>This will have exactly the same effect as making it a primary key. The *only* difference is that \d will not say it's
aprimary 
>>key... Functionally, it is completely the same thing though...
>>
>
>Another side effect of this method is that when creating foreign key
>references you will have to declare the column explicitly.
>
Yep... Missed that. Sorry... and thanks!

>
>I think to get it completely you also have to set the indisprimary bit
>on the pg_index tuple of the index...
>
Thanks for this too... I did not know ...

> Not that I'm suggesting messing
>with system catalog though: it's unportable
>
You don't need it to be portable - you are just fixing a particular
table in a particular db ONCE.

>and a little mistake can
>render your database useless.
>
I disagree... Just can't imagine a *little* mistake that would do that :-)
It would have to be a HUGE one :-)

>  Better to upgrade....
>
It depends... For example, I have about 120 Gig of stuff in my db...
I don't think, I will EVER upgrade it... (at least, not until there is a
migration utility, that will just convert existing database into the new
version) :-(

Dima