Received: from druid.net (root@druid.net [216.126.72.98]) by hub.org (8.9.3/8.9.3) with ESMTP id HAA96300 for ; Sat, 24 Jul 1999 07:49:48 -0400 (EDT) (envelope-from darcy@druid.net) Received: from localhost (1926 bytes) by druid.net via sendmail with P:stdio/R:bind_hosts/T:inet_zone_bind_smtp (sender: ) (ident using unix) id for ; Sat, 24 Jul 1999 07:49:38 -0400 (EDT) (Smail-3.2.0.104 1998-Nov-20 #4 built 1999-Apr-13) Message-Id: Subject: Re: [SQL] database design SQL prob. In-Reply-To: from Stuart Rison at "Jul 23, 1999 10:12:52 am" To: stuart@ludwig.ucl.ac.uk (Stuart Rison) Date: Sat, 24 Jul 1999 07:49:38 -0400 (EDT) From: "D'Arcy" "J.M." Cain Cc: Frederic.De.Leersnijder@pandora.be, pgsql-sql@postgreSQL.org Reply-To: pgsql-sql@postgreSQL.org X-Mailer: ELM [version 2.4ME+ PL50 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Thus spake Stuart Rison > Currently, in postgreSQL, primary keys are created as a UNIQUE index on the > field(s) that form the primary key. > > This means that there is no difference between explicitely declaring a > PRIMARY KEY in your table definition or using the CREATE UNIQUE INDEX > command. Not completely accurate. Create some tables using both methods then run the following query. SELECT pg_class.relname, pg_attribute.attname FROM pg_class, pg_attribute, pg_index WHERE pg_class.oid = pg_attribute.attrelid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[0] = pg_attribute.attnum AND pg_index.indisprimary = 't'; This will give you a list of the primary keys if you declare them as primary at creation time. The ones created with just a unique index won't be displayed. While I am on the subject, anyone know how to enhance the above query to display all the fields when a complex primary key is defined? The above assumes that all primary keys are one field per table. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.