"Confirmation" of a bug:
When I use old SQLite version 3.7.2, the offending INSERT does NOT lead to
integrity failure. This is indicative of either bug in old integrity check or
new insert. Please let me know what useful info/contribution I can make.
Another addition. The output of PRAGMA integrity_check is 31 lines (31 rows are
being inserted), all identical with first and last reading:
row 1 missing from index sqlite_autoindex_Exam_1
...
row 31 missing from index sqlite_autoindex_Exam_1
In addition, I have a trigger that updates Exam table when subject table is
updated (not on insert). I presume that trigger should not be triggered. Not
knowing what sqlite_autoindex_Exam_1 means, and seeing "Exam" I conclude the
relationship between my subject() and Exam() tables might be relevant for the
problem.
Roman
________________________________________
From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at
mailinglists.sqlite.org] on behalf of Roman Fleysher
[roman.fleys...@einstein.yu.edu]
Sent: Tuesday, May 19, 2015 12:25 PM
To: General Discussion of SQLite Database
Subject: [sqlite] index broken by insert
Dear SQLiters,
I do not really know what info to provide for sufficient information. I use
SQLite shell only for all create/insert manipulations. This insert below causes
PRAGMA integrity_check; to report missing index (what appears to be on every
inserted row):
SQLite version 3.8.8.3 2015-02-25 13:29:11
PRAGMA foreign_keys=ON;
ATTACH DATABASE 'demographics.sqlite' AS demo;
INSERT OR IGNORE INTO subject(subjectID, dob, gender)
SELECT subjectID, dob, gender FROM demo.demographics;
The subject table is defined as:
CREATE TABLE gender(
gender TEXT PRIMARY KEY NOT NULL,
description TEXT
);
INSERT OR IGNORE INTO gender (gender, description) VALUES ('Male', 'male
gender');
INSERT OR IGNORE INTO gender (gender, description) VALUES ('Female', 'female
gender');
-- force dob yyyy-mm-dd or NULL
CREATE TABLE subject(
subjectID INT PRIMARY KEY NOT NULL,
dob TEXT CHECK (dob IS date(dob)),
gender TEXT REFERENCES gender(gender) ON UPDATE CASCADE,
race TEXT,
handedness TEXT REFERENCES handedness(handedness) ON UPDATE CASCADE
);
Please let me know what other info might be useful to debug, including
debugging on my end! Integrity check on demographics.sqlite is OK.
Thank you for your help,
Roman
_______________________________________________
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
