Thread: Multiple Primary Keys

Multiple Primary Keys

From
"samsom, debra"
Date:

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.  I have created the table in Postgresql using this in my create.

CONSTRAINT "attachments_pkey"   PRIMARY KEY ("docn","issue","docna","issuea"));"

The table gets created with four primary keys, but when I copy in the data I get the following error..

"Cannot insert a duplicate key into unique index attachments_pkey"

Any ideas??

Debra Samsom
Senior Business Systems Analyst
Bristol Aerospace Ltd.
(204) 775-8331 3402
dsamsom@bristol.ca

Re: Multiple Primary Keys

From
"Josh Berkus"
Date:
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



Re: Multiple Primary Keys

From
"Josh Berkus"
Date:
Debra,

> It will be tough redesigning the database to use a surrogate key,
> cause it
> links to other tables using this key and I can't lose this
> relationship.

OK.  Just keep it in mind for the next database ;-)

> I ran the select query (looking for duplicate data) and the result
> returned
> 0 rows.  I have also run this copy using just a few records that I
> know are
> unique with the same result.
>
> Unfortunately these fields do contain spaces and funny characters,
> could
> this be the problem??

Yes, it could be.  Certain characters, such as \ and <tab> can cause
COPY to misbehave.  Can you post your full tabledef, plus a few lines
of the COPY file?

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Re: Multiple Primary Keys

From
"samsom, debra"
Date:

> Yes, it could be.  Certain characters, such as \ and <tab> can cause
> COPY to misbehave.  Can you post your full tabledef, plus a few lines
> of the COPY file?

psql dc postgres -c "create table "attachments"(
  "docn" varchar(25) NOT NULL,
  "issue" varchar(6) NOT NULL,
  "docna" varchar(4) NOT NULL,
  "issuea" varchar(20) NOT NULL,
  "applic" varchar(3),
  "whereused" varchar(1),
  CONSTRAINT "attachments_pkey"
  PRIMARY KEY ("docn","issue","docna","issuea"));"

docn                            issue     docna                           issuea

012T2100 SHT 01                   K001  DR 911380                         A001         
012T2100 SHT 01                   L001  012T2100 SHT 01 ADCN 267          -001         
012T2100 SHT 01                   L001  012T2100 SHT 01 ADCN 268          -001         
012T2100 SHT 01                   L001  012T2100 SHT 01 ADCN 270          -001         
012T2100 SHT 01                   L001  012T2100 SHT 01 ADCN 271          -001         
012T2100 SHT 01                   L001  012T2100 SHT 01 ADCN 272          -001

The duplicate error is on the third line of data ..
The headings are for your reference only, they aren't really in the file.  It's also a tab delimited file.

-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Thursday, April 18, 2002 11:07 AM
To: samsom, debra; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Multiple Primary Keys

Debra,

> It will be tough redesigning the database to use a surrogate key,
> cause it
> links to other tables using this key and I can't lose this
> relationship.

OK.  Just keep it in mind for the next database ;-)

> I ran the select query (looking for duplicate data) and the result
> returned
> 0 rows.  I have also run this copy using just a few records that I
> know are
> unique with the same result.
>
> Unfortunately these fields do contain spaces and funny characters,
> could
> this be the problem??

Yes, it could be.  Certain characters, such as \ and <tab> can cause
COPY to misbehave.  Can you post your full tabledef, plus a few lines
of the COPY file?

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Multiple Primary Keys

From
"Josh Berkus"
Date:
Debra,

> psql dc postgres -c "create table "attachments"(
>   "docn" varchar(25) NOT NULL,
>   "issue" varchar(6) NOT NULL,
>   "docna" varchar(4) NOT NULL,
>   "issuea" varchar(20) NOT NULL,
>   "applic" varchar(3),
>   "whereused" varchar(1),
>   CONSTRAINT "attachments_pkey"
>   PRIMARY KEY ("docn","issue","docna","issuea"));"
>
> docn                            issue     docna
> issuea
>
> 012T2100 SHT 01             K001 DR 911380
> A001
> 012T2100 SHT 01             L001 012T2100 SHT 01 ADCN 267
> -001
> 012T2100 SHT 01             L001 012T2100 SHT 01 ADCN 268
> -001
> 012T2100 SHT 01             L001 012T2100 SHT 01 ADCN 270
> -001
> 012T2100 SHT 01             L001 012T2100 SHT 01 ADCN 271
> -001
> 012T2100 SHT 01             L001 012T2100 SHT 01 ADCN 272
> -001

Here's the reason:  Your table definition and your data do not match.
In your tabledef, docna has 4 characters and issuea has 20.  However,
in your data, docna has 20 characters and issuea has 4.  Fix this, and
you should be able to load your data.

-Josh Berkus

Re: Multiple Primary Keys

From
"samsom, debra"
Date:

You wouldn't believe how long I stared at this and not seen my mistake.  Sometimes it just takes another pair of eyes.  Of course it works now... 

Many thanks :)

-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Thursday, April 18, 2002 12:47 PM
To: samsom, debra; 'Josh Berkus'; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Multiple Primary Keys

Debra,

> psql dc postgres -c "create table "attachments"(
>   "docn" varchar(25) NOT NULL,
>   "issue" varchar(6) NOT NULL,
>   "docna" varchar(4) NOT NULL,
>   "issuea" varchar(20) NOT NULL,
>   "applic" varchar(3),
>   "whereused" varchar(1),
>   CONSTRAINT "attachments_pkey"
>   PRIMARY KEY ("docn","issue","docna","issuea"));"
>
> docn                            issue     docna
> issuea
>
> 012T2100 SHT 01             K001 DR 911380
> A001  
> 012T2100 SHT 01             L001 012T2100 SHT 01 ADCN 267
> -001  
> 012T2100 SHT 01             L001 012T2100 SHT 01 ADCN 268
> -001  
> 012T2100 SHT 01             L001 012T2100 SHT 01 ADCN 270
> -001  
> 012T2100 SHT 01             L001 012T2100 SHT 01 ADCN 271
> -001  
> 012T2100 SHT 01             L001 012T2100 SHT 01 ADCN 272
> -001

Here's the reason:  Your table definition and your data do not match.
In your tabledef, docna has 4 characters and issuea has 20.  However,
in your data, docna has 20 characters and issuea has 4.  Fix this, and
you should be able to load your data.

-Josh Berkus

Altering psqlodbc.h

From
"Jillian Carroll"
Date:
Good Morning,

I am needing to TEXT_FIELD_SIZE 8190 to something like TEXT_FIELD_SIZE
1024*1024 in psqlodbc.h, but I can't find any information on how to do this.

Would anybody be so kind as to help me out by either offering advice, or
pointing me to a resource where I could learn how to do this?

Thank you!

--
Jillian