Firstly, the Index i_udatetime is Superfluous, you can remove it for some efficiency points. (The Unique constraint already offers an Index with udatetime as the left-most or main Index).

All I can still suggest or ask is: Are you sure there are no duplicates?

The correct SQL to check would be:

SELECT A.*
  FROM EventLog AS A
JOIN EventLog AS B ON B.udatetime=A.udatetime AND B.device=A.device AND B.code=A.code AND B.type=A.type
 WHERE B.row_id <> A.row_id

Any row that shows up is a duplicate that would violate that Unique Index.

Further to this, I'm not sure if you omitted the other fields for brevity or to explain the problem, but that INSERT query should never work since you specify all those columns as NOT NULL, then you do not give it DEFAULT values, and then you omit them from the INSERT, which means it must fail on the NOT NULL constraint.

If this is not the case, and the above query doesn't show up any rows, and the constraint still fails - mind letting us have a copy of the DB file that produces that violation?


Cheers,
Ryan


On 2016/06/28 9:37 PM, Joe Pasquariello wrote:
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;





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

Reply via email to