sqlite3中一个表怎么可能有单个主键的多个条目？

Question

sqlite3 中的表如何可能具有单个主键的多个条目？这就是我识别问题的方法：

$ sqlite3 dbName.db
sqlite> .s
CREATE TABLE 'tableName' (
   columnOne INTEGER NOT NULL,
   columnTwo INTEGER NOT NULL,
   columnThree INTEGER NOT NULL,
   columnFour INTEGER NOT NULL,
   columnFive REAL NOT NULL,
   PRIMARY KEY ( columnOne, columnTwo, columnThree, columnFour )
);
sqlite> SELECT count(1) AS nb FROM tableName GROUP BY columnOne, columnTwo, columnThree, columnFour HAVING nb > 1;
[A whole bunch of results, some with nb up to 34!]

更新 请求重复条目的示例：

$ sqlite3 observation.db
sqlite> .mode column
sqlite> .s
CREATE TABLE 'observation' (
   station INTEGER NOT NULL,
   specie INTEGER NOT NULL,
   isAvg INTEGER NOT NULL,
   date INTEGER NOT NULL,
   value REAL NOT NULL,
   PRIMARY KEY ( station, specie, isAvg, date )
);
sqlite> SELECT * FROM observation WHERE station = 105001 AND specie = 3 AND isAvg = 1 AND date = 1308650400;
station     specie      isAvg       date        value     
----------  ----------  ----------  ----------  ----------
105001      3           1           1308650400  31.0      
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266
105001      3           1           1308650400  2.42523266

@mu 太短：数据库由每小时运行一次的 tcl 脚本填充，并使用以下查询之一来插入数据：

INSERT OR REPLACE INTO observation (station, specie, isAvg, date, value) VALUES ($stationId, $speciesId, 0, $date, $value);

INSERT OR REPLACE INTO observation (station, specie, isAvg, date, value) VALUES (${stationId}, ${speciesId}, 1, ${date}, ${speciesAvg});

我只是想到了其他东西，我不知道是否这可以帮助...：

sqlite3 observation.db
sqlite> pragma integrity_check;
integrity_check
rowid 53202997 missing from index sqlite_autoindex_observation_1
rowid 53202998 missing from index sqlite_autoindex_observation_1
rowid 53202999 missing from index sqlite_autoindex_observation_1
rowid 53203000 missing from index sqlite_autoindex_observation_1
rowid 53203006 missing from index sqlite_autoindex_observation_1
rowid 53584951 missing from index sqlite_autoindex_observation_1
[...]
and more of the same (100 such lines since integrity_check stops at 100 by default..)
[...]

Answer 1

直接回答您的问题：
仅当您的主键索引有错误时才有可能。

该问题由您的integrity_check错误显示，因为sqlite3使用此索引来确定主键唯一性。当索引不好时，您将能够插入重复记录

要解决的问题：我将构建并重新填充一个新表。请记住，当您复制记录时，您将需要处理这些重复的记录。

另外，您没有显示 sqlite3 版本号，这将有助于解决问题。