Thread: alter table ad primary key
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
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
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)
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