Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g07FvdB01644 for ; Mon, 7 Jan 2002 10:57:39 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id g07Cwil36694 for pgsql-sql@postgresql.org; Mon, 7 Jan 2002 07:58:44 -0500 (EST) (envelope-from news) From: "steve boyle" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: create table with multiple unique fields Date: Mon, 7 Jan 2002 12:58:10 -0000 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 52 Message-ID: References: X-Complaints-To: news@hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 To: pgsql-sql@postgresql.org X-Archive-Number: 200201/135 X-Sequence-Number: 6223 Karl, you can either set the Primary key over all 3 fields to ensure uniquenes i.e: create table warehouse ( warehouseno int4, location varchar(3), productid varchar(1), primary key (warehouseno, location, productid) ); or define a unique index over the 3 fields after the table has been defined: create table warehouse ( warehouseno int4, location varchar(3), productid varchar(1) ); create unique index idx on warehouse (warehouseno, location, productid); The difference between the two methods is that NULLS are not allowed in Primary Keys (method 1) but would be allowed using the unique index (method 2). Hope it helps Steve Boyle "Karl Raven" wrote in message news:a1agea$qap$1@news.tht.net... > > I'd like to create a table with 3 create unique fields : > > fields > warehouseno,loc, productid, qty, qtyalllocated > > i'd like warehouseno,loc, productid to be unique.. > meaning > 2000, ABC, X - ok > 2000,ABC,Y -ok > 2000,ABC,X - reject > 2000,BCD,X - ok > > anyone knows how to do so? > > thanks > >