Обсуждение: Re: [SQL] Creating table with unique key.

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

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

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