pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Creating table with unique key.
6+ messages / 6 participants
[nested] [flat]

* Creating table with unique key.
@ 1998-08-05 21:49  Colin Dick <cdick@mail.ocis.net>
  0 siblings, 2 replies; 6+ messages in thread

From: Colin Dick @ 1998-08-05 21:49 UTC (permalink / raw)
  To: pgsql-sql

> > 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"

Can someone point me in the right direction.  Thanks.

--
Colin Dick
On Call Internet Services
cdick@mail.ocis.net





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Creating table with unique key.
@ 1998-08-05 22:46  Eric McKeown <ericm@palaver.net>
  parent: Colin Dick <cdick@mail.ocis.net>
  1 sibling, 0 replies; 6+ messages in thread

From: Eric McKeown @ 1998-08-05 22:46 UTC (permalink / raw)
  To: Colin Dick <cdick@mail.ocis.net>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Creating table with unique key.
@ 1998-08-05 23:42  Richard Lynch <lynch@lscorp.com>
  0 siblings, 2 replies; 6+ messages in thread

From: lynch@lscorp.com @ 1998-08-05 23:42 UTC (permalink / raw)
  To: Colin Dick <cdick@mail.ocis.net>; pgsql-sql

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));

>WARN:parser: parse error at or near "unique"
>
>Can someone point me in the right direction.  Thanks.

www.postgresql.org/docs/man/create_table.1.html does not seem to allow for
any spiffy keywords like UNIQUE nor PRIMARY KEY... at least not if I'm
understanding it properly.

Disclaimer:  I think the docs are wrong, not that at least one of them
won't work.

--
--
-- "TANSTAAFL" Rich lynch@lscorp.com





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Creating table with unique key.
@ 1998-08-06 12:16  Tom Good <tomg@nrnet.org>
  parent: Richard Lynch <lynch@lscorp.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Tom Good @ 1998-08-06 12:16 UTC (permalink / raw)
  To: Richard Lynch <lynch@lscorp.com>; +Cc: Colin Dick <cdick@mail.ocis.net>; pgsql-sql

On Wed, 5 Aug 1998, Richard Lynch wrote:

> www.postgresql.org/docs/man/create_table.1.html does not seem to allow for
> any spiffy keywords like UNIQUE nor PRIMARY KEY... at least not if I'm
> understanding it properly.
> 
> Disclaimer:  I think the docs are wrong, not that at least one of them
> won't work.

Rich & Colin,

Have a look below at how I convince pg to make a p_key with a check
constraint...I got this from the man page and (even better methinks)
from \h <cmd> at the isql prompt.

/***********************************************************************
#!/usr/bin/perl -w
use DBI;
use strict;

my $dbh = DBI->connect("dbi:Pg:dbname=registry", "", "", { RaiseError => 1 });

my $rv =
    $dbh->do( "
CREATE TABLE central 
(client_lname char (15) not null, client_fname char (15) not null,
client_id int not null primary key check (client_id > 100000),  
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^


a_lias char (25), home_stat char (30), home_less int,
address_1 char (40), zip_code char (10), client_dob date,
gen_der char (1), eth_nic char (8), marital_stat char (10),
diagnosis_1 char (40), diagnosis_2 char (40), dsm_1 char (6),
dsm_2 char (6), drug_use char (1), alcohol char (1),
gambler char (1), in_out char (1), t_date date,
place_ment char (25), sent_to char (25), input_by char (40));
      " );

$dbh->disconnect;
exit;
***********************************************************************/

 Cheers,
 Tom

    ----------- Sisters of Charity Medical Center ----------
                    Department of Psychiatry
                              ----     
 Thomas Good, System Administrator            <tomg@q8.nrnet.org>
 North Richmond CMHC/Residential Services     Phone: 718-354-5528
 75 Vanderbilt Ave, Quarters 8                Fax:   718-354-5056
 Staten Island, NY   10304                    www.panix.com/~ugd
                              ----     
 Powered by PostgreSQL 6.3.2 / Perl 5.004 / DBI-0.91::DBD-PG-0.69 




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Creating table with unique key.
@ 1998-08-06 14:09  Sferacarta Software <sferac@bo.nettuno.it>
  parent: Colin Dick <cdick@mail.ocis.net>
  1 sibling, 0 replies; 6+ messages in thread

From: Sferacarta Software @ 1998-08-06 14:09 UTC (permalink / raw)
  To: pgsql-sql@postgreSQL.org, Colin Dick <cdick@mail.ocis.net>

Hello Colin,

mercoledì, 5 agosto 98, you 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,
>> >         );

CD> Hi this is from a previous post regarding auto-incrementing.  I have that
CD> part working, now I am trying to create a table with a field which will
CD> not be null and will be unique to the rest of table.  Is this possible and
CD> what is the correct syntax.  I have tried and receeved the following:

CD> CREATE TABLE cust (FileNumber int4 unique not null, CompName varchar(50),
CD> FirstName varchar(50), LastName varchar(50), BirthDate varchar(8), Suite
CD> varchar(5), Address varchar(50), City varchar(20), Province varchar(20),
CD> PostalCode varchar(7), Email varchar(50), HomePhone varchar(14), WorkPhone
CD> varchar(14), FaxPhone varchar(14));
CD> WARN:parser: parse error at or near "unique"

CD> Can someone point me in the right direction.  Thanks.

CD> --
CD> Colin Dick
CD> On Call Internet Services
CD> cdick@mail.ocis.net


The syntax is correct for v6.3.2 and it works for me, maybe you have
an older release of PostgreSQL.

Best regards,
   Jose'                            mailto:sferac@bo.nettuno.it





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Creating table with unique key.
@ 1998-08-06 15:39  pierre@desertmoon.com
  parent: Richard Lynch <lynch@lscorp.com>
  1 sibling, 0 replies; 6+ messages in thread

From: pierre@desertmoon.com @ 1998-08-06 15:39 UTC (permalink / raw)
  To: lynch@lscorp.com; +Cc: Colin@desertmoon.com, Dick@desertmoon.com, <cdick@mail.ocis.net>; pgsql-sql

> 
> 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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~1998-08-06 15:39 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-08-05 21:49 Creating table with unique key. Colin Dick <cdick@mail.ocis.net>
1998-08-05 22:46 ` Eric McKeown <ericm@palaver.net>
1998-08-06 14:09 ` Sferacarta Software <sferac@bo.nettuno.it>
1998-08-05 23:42 Re: [SQL] Creating table with unique key. Richard Lynch <lynch@lscorp.com>
1998-08-06 12:16 ` Tom Good <tomg@nrnet.org>
1998-08-06 15:39 ` pierre@desertmoon.com

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox