Postgres Pro
Downloads
Home   >   mailing lists

Thread: Dual Primary keys

Dual Primary keys

From
"Bantamess"
Date:
Hello can any body tell me how to code a table with dual primary keys? The table 'borrower' has two PKs each one has been used in other tables within the same schema as single PKs
 
Here is what I have tried:
create table borrower(user_id int primary key,
issn varchar(15) primary key);
Error: create table / primary key  multiple primary keys for table 'borrower' are not allowed.
 
I also tried this:
create table borrower(user_id int ,
issn varchar(15),
primary key user_id, issn);
ERROR: parser: parse error at or near "user_id" at character 68
and
create table borrower(user_id int ,
issn varchar(15),
(primary key user_id, issn));
 ERROR: parser: parse error at or near "( " at character 56
thanks
Janet

Re: Dual Primary keys

From
Bruno Wolff III
Date:
On Wed, Feb 04, 2004 at 15:15:45 -0000,
  Bantamess <bantamess@yahoo.co.uk> wrote:
> Hello can any body tell me how to code a table with dual primary keys? The table 'borrower' has two PKs each one has
beenused in other tables within the same schema as single PKs 

Use UNIQUE and NOT NULL constraints on one of the two keys. In references
to that key, you will need to specify the column name as the default
column name is the primary key.

Re: Dual Primary keys

From
Tom Lane
Date:
"Bantamess" <bantamess@yahoo.co.uk> writes:
> Hello can any body tell me how to code a table with dual primary keys?

Do you mean a two-column composite primary key?  Two independent primary
keys is nonsensical --- you should pick just one of them to be
considered the "primary" key.

If you want a composite primary key, the syntax is
    primary key (f1, f2)

            regards, tom lane

Re: Dual Primary keys

From
Josh Berkus
Date:
Janet,

> I also tried this:
> create table borrower(user_id int ,
> issn varchar(15),
> primary key user_id, issn);
> ERROR: parser: parse error at or near "user_id" at character 68

This is, I think, what you want.  However, you have a syntax error in your
primary key constraint declaration:

CONSTRAINT borrower_pk PRIMARY KEY (user_id, issn)

Also, both user_id and issn should be declared NOT NULL.

Overally, I suggest picking up an "introduction to SQL book", such as "SQL
Queries for Mere Mortals."

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco