Does anyone know why a primary key would not be used?

-----Original Message-----
From: Rick DeBay
Sent: Friday, October 22, 2004 2:23 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Duplicate primary keys v1.5.1



(originally sent to wrong group)

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