I dropped the PK, deleted all the rows, recreated the PK, and reimported
the data:

Select count(*),count(distinct gpi) from mddb_gpi
10077 10077

MDDB_GPI (239)
Primary pointer page: 106292, Index root page: 106293
Data pages: 236, data page slots: 236, average fill: 78%
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 173
80 - 99% = 62

Index PK_MDDB_GPI (0)
Depth: 2, leaf buckets: 37, nodes: 10169
Average data length: 4.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 14
60 - 79% = 12
80 - 99% = 11

Any ideas why it failed the first time?

-----Original Message-----
From: Rick DeBay
Sent: Friday, October 22, 2004 12:30 PM
To: Firebird-Java@yahoogroups.com
Subject: [Firebird-Java] Duplicate primary keys v1.5.1


I used a stored procedure to import a lot of data, and got quite a
surprise when I viewed it. Every imported record was in the table,
including duplicates! Looking at the statistics for the primary key, it
shows that it wasn't used. I wasn't aware that a PK could be turned
off, or how to turn it back on now that it's off. Gfix -v -n didn't
return any messages.

CREATE TABLE MDDB_GPI
(
GPI D_GPI NOT NULL,
GENERICNAME VARCHAR( 60) NOT NULL,
CONSTRAINT PK_MDDB_GPI PRIMARY KEY (GPI)
);

select count(gpi) from MDDB_GPI
100820
select count(distinct gpi) from MDDB_GPI
10169

CREATE PROCEDURE GPI_INS_UPDT (
GPI Decimal(14,0),
GENERICNAME VarChar(60))
AS
BEGIN
INSERT INTO MDDB_GPI (
GPI,
GENERICNAME)
VALUES (
:GPI,
:GENERICNAME);
WHEN SQLCODE -803 DO
UPDATE MDDB_GPI SET
GENERICNAME = :GENERICNAME
WHERE
GPI = :GPI;
END

MDDB_GPI (239)
Primary pointer page: 106292, Index root page: 106293
Data pages: 2161, data page slots: 2161, average fill: 72%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 2160
80 - 99% = 0

Index PK_MDDB_GPI (0)
Depth: 0, leaf buckets: 0, nodes: 0
Average data length: 0.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0

Rick DeBay
Senior Software Developer
RxStrategies.net




Yahoo! Groups Links