Re: Stupid question on INDEXES

"Giorgio Ponza" <giorgio(at)opla(dot)it> writes:
> I'd like to know if i think in the right way. With
> CREATE TABLE Test1 (
> my_id int,
> descr text,
> primary key (my_id))
> Postgres automatically creates an index named test1_pkey.
> If i use
> CREATE TABLE Test1 (
> my_id int,
> descr text)
> without specifying a pkey, i can write
> create unique index i_test1_pkey on (my_id).
> The only difference is that in the 2nd case i can use the name i want, or
> the usage is different?

There are two things that are different:

1. PRIMARY KEY implies NOT NULL. You'd need to declare the column as
NOT NULL in the second CREATE TABLE to have exactly equivalent
constraints.

2. When a PRIMARY KEY is declared, the column(s) become the default
columns for a FOREIGN KEY declaration in another table, so you could
write "REFERENCES Test1" without having to mention my_id as the
referenced column. Without a PRIMARY KEY, you'll have to mention my_id
explicitly. But this is only a notational convenience.

regards, tom lane