Hello,
I'm a novice user of Sqlite, and could use some help. The code below is
intended to update a simple table to a new format. The changes include
reordering columns, dropping one column, and adding a new "status"
column (which is always 0). A constraint violation is being reported on
UNIQUE constraint for the new table. I assume this means there is a row
in the existing table that violates the constraint imposed on the new
table, but I've queried the existing table for rows that would violate
the constraint, and I don't find any. Is there something else that could
cause the violation?
Thanks,
Joe
BEGIN TRANSACTION;
DROP TABLE IF EXISTS TempEventLog;
ALTER TABLE EventLog RENAME TO TempEventLog;
CREATE TABLE IF NOT EXISTS EventLog(
rowid INTEGER PRIMARY KEY,
udatetime INTEGER NOT NULL,
device CHAR(16) NOT NULL COLLATE NOCASE,
localtime CHAR(32) NOT NULL COLLATE NOCASE,
code INTEGER NOT NULL,
type CHAR(16) NOT NULL COLLATE NOCASE,
text CHAR(64) NOT NULL COLLATE NOCASE,
status INTEGER NOT NULL,
UNIQUE(udatetime,device,code,type,status)
);
CREATE INDEX IF NOT EXISTS i_udatetime ON EventLog(udatetime);
CREATE INDEX IF NOT EXISTS i_code ON EventLog(code);
CREATE INDEX IF NOT EXISTS i_device_code ON EventLog(device,code);
INSERT OR ROLLBACK INTO EventLog (
udatetime, device, localtime, code, type, text, status )
SELECT udatetime, device, localtime, code, type, text, 0
FROM TempEventLog ORDER BY rowid;
DROP TABLE TempEventLog;
PRAGMA user_version = 1;
COMMIT;
--
Joe Pasquariello
Fenway Systems, LLC
2980 College Ave, Suite 7
Berkeley, CA 94705
510-665-4355
j...@fenway.com
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
