Re: [SQL] Creating table with unique key.

On Wed, 5 Aug 1998, Colin Dick wrote:

> Date: Wed, 5 Aug 1998 14:49:13 -0700 (PDT)
> From: Colin Dick <cdick@mail.ocis.net>
> To: pgsql-sql@postgreSQL.org
> Subject: [SQL] Creating table with unique key.
> 
> > > CREATE TABLE cliente (
> > >         name    varchar(100) UNIQUE NOT NULL,
> > >         username        varchar(8) NOT NULL ,
> > >         key     int4 NOT NULL DEFAULT nextval('key_s') PRIMARY KEY,
> > >         );
> 
> 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));
> WARN:parser: parse error at or near "unique"

CREATE TABLE cust (FileNumber int4 PRIMARY KEY NOT NULL, ...

I think that should fix it.  You could also create a unique index on
individual fields in order to enforce their uniqueness.

CREATE [UNIQUE] INDEX <indexname> on <class_name> (fieldname)

Do a \h on create index for more info...

eric

> 
> Can someone point me in the right direction.  Thanks.
> 
> --
> Colin Dick
> On Call Internet Services
> cdick@mail.ocis.net
> 
> 
> 

_______________________
Eric McKeown
ericm@palaver.net
http://www.palaver.net