Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBJFCAm21668 for ; Wed, 19 Dec 2001 10:12:10 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fBJFBqi29524; Wed, 19 Dec 2001 10:11:52 -0500 (EST) To: "Giorgio Ponza" cc: pgsql-novice@postgresql.org Subject: Re: Stupid question on INDEXES In-reply-to: <002601c1888d$9ac2aba0$c801a8c0@opla.it> References: <002601c1888d$9ac2aba0$c801a8c0@opla.it> Comments: In-reply-to "Giorgio Ponza" message dated "Wed, 19 Dec 2001 14:03:50 +0100" Date: Wed, 19 Dec 2001 10:11:51 -0500 Message-ID: <29521.1008774711@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200112/92 X-Sequence-Number: 2635 "Giorgio Ponza" 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