Обсуждение: Multiple Primary Keys
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
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
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
> 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
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
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
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