pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Stupid question on INDEXES
2+ messages / 2 participants
[nested] [flat]

* Stupid question on INDEXES
@ 2001-12-19 13:03  Giorgio Ponza <giorgio@opla.it>
  0 siblings, 1 reply; 2+ messages in thread

From: Giorgio Ponza @ 2001-12-19 13:03 UTC (permalink / raw)
  To: pgsql-novice@postgresql.org

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?

Many thanks

Giorgio





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Stupid question on INDEXES
@ 2001-12-19 15:11  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Giorgio Ponza <giorgio@opla.it>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2001-12-19 15:11 UTC (permalink / raw)
  To: Giorgio Ponza <giorgio@opla.it>; +Cc: pgsql-novice@postgresql.org

"Giorgio Ponza" <giorgio@opla.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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-12-19 15:11 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-19 13:03 Stupid question on INDEXES Giorgio Ponza <giorgio@opla.it>
2001-12-19 15:11 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox