Skip site navigation (1) Skip section navigation (2)

Re: Multiple Primary Keys

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: "samsom, debra" <dsamsom(at)bristol(dot)ca>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Multiple Primary Keys
Date: 2002-04-18 15:35:02
Message-ID: web-1375947@davinci.ethosmedia.com (view raw or flat)
Thread:
Lists: pgsql-novice
Debra,

> Currently I have a table on Microsoft Sequel Server that I wish to
> port over
> to Postgresql.   This table has four primary keys and it's the
> combination
> of the four fields that make the record unique.  

Just to get your terminology straight:  What you have is a *single*
primary key, consisting of four fields.  You cannot have more than one
primary key for a table.  I would strongly suggest that you re-consider
your database design: composite primary keys (which is what you have)
are a *lot* of trouble.

A surrogate key (such as an auto-incrementing integer) would be much
easier to handle.  For more information, I strongly reccomend the book
"Database Design for Mere Mortals."

> "Cannot insert a duplicate key into unique index attachments_pkey"

Yes.  You have duplicate data somewhere in the table.  The primary key
must be unique.  Try this:

SELECT "docn","issue","docna","issuea", count(*) FROM attachments
GROUP BY "docn","issue","docna","issuea"
HAVING count(*) > 1;

This should list all of your duplicates.    If you get a "NULL Value
Eliminated From Aggregate" warning, that means that one or more of
those columns has a NULL value, which must either be filled in, or you
must exclude it fromm the Primary Key.

-Josh Berkus



In response to

Responses

pgsql-novice by date

Next:From: Josh BerkusDate: 2002-04-18 16:06:59
Subject: Re: Multiple Primary Keys
Previous:From: samsom, debraDate: 2002-04-18 15:01:45
Subject: Multiple Primary Keys

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group