We have an SQLite database with a table that has about a million rows.
We do a lot of (thousands) of deletes and inserts in this table.
Recently, deleting rows has become slow. I assumed the slow down was
a result of fragmentation or wasted space due to the deletes.
Therefore, I thought vacuuming would fix it. While trying to VACUUM
the database, I got this error from sqlite3:
"SQL error: constraint failed"
Does anyone have any ideas what constraint could be failing and how to fix it?
I've attached the schema of the db.. if anyone thinks its relevant.
Thanks,
-John
CREATE TABLE category (taxonomy_id INTEGER NOT NULL,
category_uid VARCHAR NOT NULL UNIQUE,
version VARCHAR NOT NULL,
category_state_id INTEGER NOT NULL,
modification_date DATE DEFAULT NULL,
PRIMARY KEY (taxonomy_id, category_uid, version));
CREATE TABLE category_meta_information (taxonomy_id INTEGER NOT NULL,
category_id INTEGER NOT NULL,
name VARCHAR NOT NULL,
value VARCHAR,
data_type_id INTEGER NOT NULL,
PRIMARY KEY (taxonomy_id, category_id, name));
CREATE TABLE category_state (state VARCHAR NOT NULL UNIQUE DEFAULT 'training');
CREATE TABLE data_type (type VARCHAR UNIQUE DEFAULT 'string');
CREATE TABLE feature_type (feature_type VARCHAR NOT NULL UNIQUE);
CREATE TABLE taxonomy_description (name VARCHAR UNIQUE,
version VARCHAR DEFAULT 'current',
featuresets VARCHAR DEFAULT 'CWordPairFeatureSet_Object',
modification_date DATE DEFAULT NULL,
PRIMARY KEY (name, version));
CREATE TABLE taxonomy_meta_information (name VARCHAR NOT NULL,
value VARCHAR,
data_type_id INTEGER NOT NULL,
taxonomy_id INTEGER NOT NULL);
CREATE UNIQUE INDEX tempindex ON taxonomy_meta_information ( taxonomy_id,name );
CREATE TRIGGER delete_category BEFORE DELETE ON category
BEGIN
DELETE FROM category_meta_information WHERE category_id=old.rowid;
END;
CREATE TRIGGER delete_category_meta_moddate AFTER DELETE ON
category_meta_information
BEGIN
UPDATE category SET modification_date = DATETIME('NOW') WHERE rowid =
old.category_id;
END;
CREATE TRIGGER delete_category_moddate AFTER DELETE ON category
BEGIN
UPDATE taxonomy_description SET modification_date = DATETIME('NOW') WHERE
rowid = old.taxonomy_id;
END;
CREATE TRIGGER delete_taxonomy BEFORE DELETE ON taxonomy_description
BEGIN
DELETE FROM category WHERE taxonomy_id=old.rowid;
DELETE FROM taxonomy_meta_information WHERE taxonomy_id=old.rowid;
END;
CREATE TRIGGER insert_category_meta_moddate AFTER INSERT ON
category_meta_information
BEGIN
UPDATE category SET modification_date = DATETIME('NOW') WHERE rowid =
new.category_id;
END;
CREATE TRIGGER insert_category_moddate AFTER INSERT ON category
BEGIN
UPDATE category SET modification_date = DATETIME('NOW') WHERE rowid =
new.rowid;
UPDATE taxonomy_description SET modification_date = DATETIME('NOW') WHERE
rowid = new.taxonomy_id;
END;
CREATE TRIGGER insert_taxonomy_moddate AFTER INSERT ON taxonomy_description
BEGIN
UPDATE taxonomy_description SET modification_date = DATETIME('NOW') WHERE
rowid = new.rowid;
END;
CREATE TRIGGER update_category AFTER UPDATE OF rowid ON category
BEGIN
UPDATE category_meta_information SET category_id=new.rowid WHERE
category_id=old.rowid;
END;
CREATE TRIGGER update_category_meta_moddate AFTER UPDATE ON
category_meta_information
BEGIN
UPDATE category SET modification_date = DATETIME('NOW') WHERE rowid =
new.category_id;
END;
CREATE TRIGGER update_category_moddate AFTER UPDATE ON category
BEGIN
UPDATE category SET modification_date = DATETIME('NOW') WHERE rowid =
new.rowid;
UPDATE taxonomy_description SET modification_date = DATETIME('NOW') WHERE
rowid = new.taxonomy_id;
END;
CREATE TRIGGER update_taxonomy_description AFTER UPDATE OF rowid ON
taxonomy_description
BEGIN
UPDATE category SET taxonomy_id=new.rowid WHERE taxonomy_id=old.rowid;
UPDATE taxonomy_meta_information SET taxonomy_id=new.rowid WHERE
taxonomy_id=old.rowid;
END;
CREATE TRIGGER update_taxonomy_moddate AFTER UPDATE ON taxonomy_description
BEGIN
UPDATE taxonomy_description SET modification_date = DATETIME('NOW') WHERE
rowid = new.rowid;
END;
