Duplication can also result as part of the - in process - moving of rows. To change the order of [1,2,3,4] to [1,3,2,4] there is(can be) a state that is [1,2,2,4] before the second part that sets three back into 2.
This is a point where NULL can be useful to make the transition [1,2,NULL,4] [1,3,NULL,4] [1,3,2,4] ( in which case NOT NULL is NOT recommended :) ) It can be some other unique value like -1; but in either case; some databases only allow one NULL in unique anyway... and if you have two processes that are both in partial steps of execution you can conflict that way too. wrapping that in a transaction should suspend constraint checks until a commiit (IMO don't know if that's fact) (or partial duplicates can happen if you get an exception that faults and fails to complete the operation) On Tue, Jun 28, 2016 at 2:18 PM, Joe Pasquariello <j...@fenway.com> wrote: > 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 >> >> > Thank you, Ryan. I'm embarassed to say I don't know how to reply to your > response. When I posted my original question, I simply sent email to the > mailing list. I viewed your response on Nabble, but it doesn't seem to > allow me to reply. How can I view your response and reply somewhere other > than Nabble? To create this email, I copied and pasted from Nabble. I'm > afraid it will show up as a new topic. There has to be a better way. > > To the point, your query to find duplicate records showed there were some > duplicates after all. > > I removed the duplicates and the SQL as shown in my original post worked. > I don't know what you mean about the insert statement being incorrect. It > explicitly sets all fields in the new table except rowid. My understanding > was that since rowid is INTEGER PRIMARY KEY, inserts to the new table would > get new rowid values, in the order inserted, which is what I want, and it > seems to be doing that correctly. > > The query I was using to find duplicates is one that I found on > StackOverflow. I'm very new to SQL. Can you tell me why this query returns > no records while yours does? If I change the last clause to having > count(*)=1, the result has the same number of rows as the table. I thought > that meant it was doing what I wanted, but obviously it doesn't. > > select udatetime,device,localtime,code,type,text,0 as status, count(*) as > the_count > from eventlog > group by udatetime,device,code,type,status > having count(*) > 1 > > > > > > > >> 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
