Обсуждение: designating a column as primary key after creation

Why does postgres choke on the following:

alter table mytable add constraint mycolumn_pk primary key(mycolumn);

is this possible in a postgres database?  if not, what's an easy
workaround, i really need to have this column as primary key.

Thanks!

-Fran

On Wed, 12 Aug 1998, Fran Fabrizio wrote:

>
> Why does postgres choke on the following:
>
> alter table mytable add constraint mycolumn_pk primary key(mycolumn);
>
> is this possible in a postgres database?  if not, what's an easy
> workaround, i really need to have this column as primary key.

Unfortunately the syntax you wanted to use is not allowed in PostgresSQL
as well as
1. alter table <tablename> drop column <colname>
and
2. alter table <tablename> modify column ....

Nevertheless, there is at least one workaround for your case: you can
create (at any time!) unique index on your table using any column or
column combination from already existing table. The only weakness of this
method is that NULL values are not forbidded in unique index , so you can
lost uniquness if there will be some rows with NULLs in key column(s).
(note, that each NULL is treated as new  value, I mean one NULL is
note equal to another one!)

for details refer to create_index(l) and drop_index(l) man pages.

Al.

At 10:53 +0300 on 13/8/98, Aleksey Dashevsky wrote:


> (note, that each NULL is treated as new  value, I mean one NULL is
> note equal to another one!)

Hey, wait a second. Wasn't this supposed to be fixed in 6.3?

I really hate the way nulls are treated in 6.2.1, and I'm pushing my
sysadmin hard to update the version, because I want sorts on two columns
not to be confused when there are nulls in the first column.

(That is, if I "ORDER BY heb_term, eng_term" - and there are possible NULLs
in heb_term, I expect all the nulls to be considered the same value, so
that all the rows with NULL in their heb_term will be sorted by eng_term.
Otherwise I have to make two separate queries).

I also want to be able to compare fields, and get a correct result if the
two fields are null. For example, I create two tables. The snapshot table
shows the rows in the original table, as they were at a given time, and I
want to be able to compare the row from the snapshot table with the
corresponding row in the original table, and see if anything has changed.
But if one of the fields is null, I would be comparing NULL to NULL, and in
6.2.1, it would look as if the two rows are different!

Anyway, I was sure this problem was fixed in 6.3.x... Can anyone confirm or
deny?

I do like the fact that I can have as many nulls as I need in a column with
a unique index. This should stay like that (I think we discussed it a long
time ago).

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma