Re: [sqlite] UNIQUE constraint violation

[R Smith]

Additionally - You should add NOT NULL to the PRIMARY KEY constraint 
too, since a backwards-compatible peculiarity in SQLite allows the PK to 
have NULL values if not explicitly disallowed.

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;



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users