Received: from desertmoon.com (qmailr@desertmoon.com [198.182.124.2]) by hub.org (8.8.8/8.7.5) with SMTP id LAA10098 for ; Thu, 6 Aug 1998 11:35:49 -0400 (EDT) From: pierre@desertmoon.com Received: (qmail 14993 invoked by uid 100); 6 Aug 1998 15:39:53 -0000 Message-ID: <19980806153953.14992.qmail@desertmoon.com> Date: 6 Aug 1998 08:39:53 -0700 Subject: Re: [SQL] Creating table with unique key. To: lynch@lscorp.com (Richard Lynch) Date: Thu, 6 Aug 1998 08:39:52 -0700 (MST) Cc: Colin@desertmoon.com, Dick@desertmoon.com, , pgsql-sql@postgreSQL.org In-Reply-To: from "Richard Lynch" at Aug 5, 98 06:42:03 pm X-Mailer: ELM [version 2.4 PL25] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit > > At 2:49 PM 8/5/98, Colin Dick wrote: > >> > CREATE TABLE cliente ( > >> > name varchar(100) UNIQUE NOT NULL, > >> > username varchar(8) NOT NULL , > >> > key int4 NOT NULL DEFAULT nextval('key_s') PRIMARY KEY, > > There should be no comma (,) in the last field description. > > >> > ); > > > >Hi this is from a previous post regarding auto-incrementing. I have that > >part working, now I am trying to create a table with a field which will > >not be null and will be unique to the rest of table. Is this possible and > >what is the correct syntax. I have tried and receeved the following: > > > > CREATE TABLE cust ( > FileNumber int4 unique not null, > CompName varchar(50), > FirstName varchar(50), > LastName varchar(50), > BirthDate varchar(8), > Suite varchar(5), > Address varchar(50), > City varchar(20), > Province varchar(20), > PostalCode varchar(7), > Email varchar(50), > HomePhone varchar(14), > WorkPhone varchar(14), > FaxPhone varchar(14)); I use the UNIQUE keyword on an index to force a uniqueness in a table field. eg. create unique index pindex_id on product using btree (product_id); Where the field product_id is defined in the table as being not allowing a null value. This works like a charm.... -=pierre