Thread: alter table ad primary key

alter table ad primary key

From
Christoph Dalitz
Date:
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?

Did someone already implement a workaround in form of a stored
procedure that does the following:
  - copy the table entirely to a temporary table
  - remember all indices, constraints, rules and triggers on the old table
    (is that possible at all?)
  - drop the old table
  - recreate the table with a primary key
  - copy the temp table bakc
  - drop the temp table
?

Thanks,

Christoph Dalitz

Re: alter table ad primary key

From
Dmitry Tkach
Date:
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?
>
> Did someone already implement a workaround in form of a stored
> procedure that does the following:
>   - copy the table entirely to a temporary table
>   - remember all indices, constraints, rules and triggers on the old table
>     (is that possible at all?)
>   - drop the old table
>   - recreate the table with a primary key
>   - copy the temp table bakc
>   - drop the temp table
> ?
>


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 a
primary
key... Functionally, it is completely the same thing though...

I hope, it helps...

Dima


Re: alter table ad primary key

From
Alvaro Herrera
Date:
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.

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

I think to get it completely you also have to set the indisprimary bit
on the pg_index tuple of the index...  Not that I'm suggesting messing
with system catalog though: it's unportable and a little mistake can
render your database useless.  Better to upgrade....


--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Pensar que el espectro que vemos es ilusorio no lo despoja de espanto,
sólo le suma el nuevo terror de la locura" (Perelandra, CSLewis)

Re: alter table ad primary key

From
Dmitry Tkach
Date:
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