Re: [sqlite] UNIQUE constraint fails when setting legacy_file_format=true

Addressed by https://www.sqlite.org/src/info/713caa382cf7ddef On 5/1/19, Manuel Rigger wrote: > Hi everyone, > > I think that I found a bug that occurs when setting legacy_file_format to > true and calling REINDEX, which then results in "Error: UNIQUE constraint > failed: index 'index_0'" in the

[sqlite] UNIQUE constraint fails when setting legacy_file_format=true

Hi everyone, I think that I found a bug that occurs when setting legacy_file_format to true and calling REINDEX, which then results in "Error: UNIQUE constraint failed: index 'index_0'" in the specific example below: CREATE TABLE test (c0, c1 TEXT); CREATE UNIQUE INDEX IF NOT EXISTS index_0 ON

Re: [sqlite] Unique Constraint Failed

Dumb question: are you sure you're only inserting one record at a time? Is it possible you're inserting records so fast that the timestamp is the same for two of them? On Fri, Sep 14, 2018 at 3:30 PM Andrew Stewart wrote: > Hi, > I am having problems with a database reporting

Re: [sqlite] Unique Constraint Failed

ndrew Stewart >Sent: Friday, 14 September, 2018 14:14 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Unique Constraint Failed > >Hi all, >I realize that this is the constraint that is >failing. The data is very large, encrypted and at a customer's sit

Re: [sqlite] Unique Constraint Failed

I'm just beginning to look at sqlite so this approach might not apply. In instances where I had no control on the input stream and an occasional duplicate could occur I fed the input stream into a temp table then used a select from that temp with a count function and a group by ID, DateTime

Re: [sqlite] Unique Constraint Failed

Hi, On Fri, Sep 14, 2018 at 3:14 PM Andrew Stewart wrote: > > Hi all, > I realize that this is the constraint that is failing. The > data is very large, encrypted and at a customer's site - not easy to use an > external program to view or to transfer to my office. > >

[sqlite] Unique Constraint Failed

Hi all, I realize that this is the constraint that is failing. The data is very large, encrypted and at a customer's site - not easy to use an external program to view or to transfer to my office. What I am wondering is if there are any limits on the Unique

Re: [sqlite] Unique Constraint Failed

On 14 Sep 2018, at 8:56pm, Andrew Stewart wrote: > CREATE TABLE dataStreamRecord ( > fwParameterID INTEGER NOT NULL, > dateTime INTEGER NOT NULL, > data INTEGER NOT NULL, > UNIQUE ( > fwParameterID, > dateTime > ) > ); Well, there is only one UNIQUE

[sqlite] Unique Constraint Failed

Hi Simon, I am having a problem receiving the emails and therefore cannot do this as a reply to the message. I am seeing your responses on the forum site. Below is the DDL for creating the table. It should not be possible for 2 elements to have the same

Re: [sqlite] Unique Constraint Failed

On 14 Sep 2018, at 8:29pm, Andrew Stewart wrote: >I am having problems with a database reporting Unique > Constraint Failed when doing an insert. > Table consists of 3 columns: > ID, DateTime, data > Constraint is on ID,DateTime. > >DateTime trying to enter is

[sqlite] Unique Constraint Failed

Hi, I am having problems with a database reporting Unique Constraint Failed when doing an insert. Table consists of 3 columns: ID, DateTime, data Constraint is on ID,DateTime. DateTime trying to enter is current time. File is 200+ GB.

Re: [sqlite] unique constraint

Thanks for the responses. Just a brain error. Not sure what I was thinking :) On Tue, May 15, 2018 at 6:55 PM R Smith wrote: > > On 2018/05/16 1:25 AM, Mark Wagner wrote: > > OK, at the risk of exposing my lack of edification... > > Sometimes you pay the price for

Re: [sqlite] unique constraint

On 2018/05/16 1:25 AM, Mark Wagner wrote: OK, at the risk of exposing my lack of edification... Sometimes you pay the price for taking a risk. :) create table t (k integer primary key, s text UNIQUE); Did you by any chance assume the "UNIQUE" constraint to cover both the fields k and s?

Re: [sqlite] unique constraint

>OK, at the risk of exposing my lack of edification, I'm wondering if >someone can explain why this simple test of unique column constraints >doesn't work. At least it doesn't work as I expected it would (i.e. >that >the second insert would yield a unique constraint violation). > >create table t

Re: [sqlite] unique constraint

I am confused. Too much experimenting :( On Tue, May 15, 2018 at 4:55 PM Simon Slavin wrote: > On 16 May 2018, at 12:25am, Mark Wagner wrote: > > > I'm wondering if > > someone can explain why this simple test of unique column constraints > > doesn't

Re: [sqlite] unique constraint

On 16 May 2018, at 12:25am, Mark Wagner wrote: > I'm wondering if > someone can explain why this simple test of unique column constraints > doesn't work. At least it doesn't work as I expected it would (i.e. that > the second insert would yield a unique constraint violation). >

[sqlite] unique constraint

OK, at the risk of exposing my lack of edification, I'm wondering if someone can explain why this simple test of unique column constraints doesn't work. At least it doesn't work as I expected it would (i.e. that the second insert would yield a unique constraint violation). create table t (k

Re: [sqlite] UNIQUE Constraint failure better diagnostics

+1 on my part On Sun, Aug 14, 2016 at 9:59 AM, Василий Кудрявцев wrote: > Hi! > > > > I would like to propose an enhancement to SQLite diagnostics in case of > unique constraint failure. > > Currently the message does not contain the name of constraint in question, > only

[sqlite] UNIQUE Constraint failure better diagnostics

Hi! I would like to propose an enhancement to SQLite diagnostics in case of unique constraint failure. Currently the message does not contain the name of constraint in question, only columns. For example: sqlite> CREATE TABLE c_test (c0 INTEGER, c1 INTEGER, c2 INTEGER, CONSTRAINT U_X1

[sqlite] UNIQUE Constraint failure better diagnostics

Hi! I would like to propose an enhancement to SQLite diagnostics in case of unique constraint failure. Currently the message does not contain the name of constraint in question, only columns. For example: sqlite> CREATE TABLE c_test (c0 INTEGER, c1 INTEGER, c2 INTEGER, CONSTRAINT U_X1

Re: [sqlite] UNIQUE constraint violation

On Tue, 28 Jun 2016 19:19:43 -0700 J Decker wrote: > 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.

Re: [sqlite] UNIQUE constraint violation

On 2016/06/29 4:53 PM, Joe Pasquariello wrote: On 6/29/2016 6:13 AM, R Smith wrote: Very happy this is sorted for you. Note that Synchronous=OFF should only ever be used on a read-only DB (or at least one where writing only happens when you the user/DB Admin actively makes changes via

Re: [sqlite] UNIQUE constraint violation

On 29 Jun 2016, at 3:53pm, Joe Pasquariello wrote: > Okay, thanks. I thought it was generally preferable to use "lower" levels of > synchronous. No. Other way around. Two principles to guide you through the use of PRAGMAs: A) The default settings are meant to be safe,

Re: [sqlite] UNIQUE constraint violation

On 6/29/2016 6:13 AM, R Smith wrote: Very happy this is sorted for you. Note that Synchronous=OFF should only ever be used on a read-only DB (or at least one where writing only happens when you the user/DB Admin actively makes changes via queries. For anything that should run autonomously,

Re: [sqlite] UNIQUE constraint violation

On 2016/06/29 2:48 PM, Joe Pasquariello wrote: starting from the original, corrupted DB... integrity_check row 18029 missing from index sqlite_autoindex_EventLog_1 row 18030 missing from index sqlite_autoindex_EventLog_1 row 18031 missing from index sqlite_autoindex_EventLog_1 row 18032

Re: [sqlite] UNIQUE constraint violation

On 6/29/2016 1:04 AM, R Smith wrote: These are the duplicate records. This index is associated with a UNIQUE constraint on the original table. Is there a way to clean it up? That means your DB is corrupted by some method. It's not bad yet, just missing Index entries. Make a copy of the

Re: [sqlite] UNIQUE constraint violation

On 29 Jun 2016, at 5:36am, Joe Pasquariello wrote: > These are the duplicate records. This index is associated with a UNIQUE > constraint on the original table. Is there a way to clean it up? Unfortunately it's an automatic index and I hesitate to manipulate it manually in

Re: [sqlite] UNIQUE constraint violation

On 2016/06/29 6:36 AM, Joe Pasquariello wrote: On 6/28/2016 4:52 PM, Simon Slavin wrote: On 28 Jun 2016, at 11:22pm, Joe Pasquariello wrote: SELECT udatetime,typeof(udatetime),udatetime-1415000934 FROM eventlog where device like '%M14' and udatetime=1415000934 udatetime

Re: [sqlite] UNIQUE constraint violation

On 6/28/2016 4:52 PM, Simon Slavin wrote: On 28 Jun 2016, at 11:22pm, Joe Pasquariello wrote: select rowid, device, hex(device), localtime, udatetime from eventlog where device like '%M14' and udatetime=1415000934 rowiddevicehex(device)localtimeudatetime

Re: [sqlite] UNIQUE constraint violation

If you need to temporarily violate checks in order to rearrange your schema, I draw your attention to these two: Obviously, once you're done rearranging/importing you

Re: [sqlite] UNIQUE constraint violation

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]

Re: [sqlite] UNIQUE constraint violation

On 28 Jun 2016, at 11:22pm, Joe Pasquariello wrote: > select rowid, device, hex(device), localtime, udatetime > from eventlog where device like '%M14' and udatetime=1415000934 > > rowiddevicehex(device)localtimeudatetime > 18031M144D31342014-11-03

Re: [sqlite] UNIQUE constraint violation

On 6/28/2016 3:05 PM, Simon Slavin wrote: SELECT device,HEX(device) FROM myTable WHERE Thanks, Simon. Now it's getting really strange. Two queries and their results are shown below. The value of "device" seems to be the same in both rows. One query includes the field "localtime" and the

Re: [sqlite] UNIQUE constraint violation

On 6/28/2016 2:35 PM, R Smith wrote: On 2016/06/28 11:21 PM, R Smith wrote: CREATE TABLE t(A TEXT COLLATE NOCASE, BTEXT COLLATE NOCASE); Correction: I meant to paste that line below saying it is what you need to fix the grouping, and added it to the script in stead. The original meant to

Re: [sqlite] UNIQUE constraint violation

On 28 Jun 2016, at 11:03pm, Joe Pasquariello wrote: > Somehow, the two "device" values shown in the table below are not the same, > and I have no idea why. SELECT device,HEX(device) FROM myTable WHERE ... Simon. ___ sqlite-users

Re: [sqlite] UNIQUE constraint violation

On 2016/06/28 11:21 PM, R Smith wrote: CREATE TABLE t(A TEXT COLLATE NOCASE, BTEXT COLLATE NOCASE); Correction: I meant to paste that line below saying it is what you need to fix the grouping, and added it to the script in stead. The original meant to be without the NOCASE collations.

Re: [sqlite] UNIQUE constraint violation

On 2016/06/28 10:52 PM, j...@fenway.com wrote: 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

[sqlite] UNIQUE constraint violation

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:

Re: [sqlite] UNIQUE constraint violation

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;

Re: [sqlite] UNIQUE constraint violation

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

[sqlite] UNIQUE constraint violation

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

[sqlite] `UNIQUE` constraint allows multiple NULL: is this expected?

On Wed, 27 Jan 2016 14:38:41 -0500 Richard Hipp wrote: > > See https://www.sqlite.org/nulls.html > The most important part to me, is this one: > The fact that NULLs are distinct for UNIQUE columns but are indistinct for > SELECT DISTINCT and UNION continues to be puzzling. It seems that

[sqlite] `UNIQUE` constraint allows multiple NULL: is this expected?

On Wed, 27 Jan 2016 14:17:49 -0500 Igor Tandetnik wrote: > On 1/27/2016 2:10 PM, Yannick Duch?ne wrote: > > `UNIQUE` constraint allows multiple NULL: is this expected? > > http://www.sqlite.org/lang_createtable.html > "For the purposes of UNIQUE constraints, NULL values are considered >

[sqlite] `UNIQUE` constraint allows multiple NULL: is this expected?

Hi people out there, I initially subscribed for another question (which I will post later), when I came to something which surprised me. Given this: CREATE TABLE test (value TEXT UNIQUE) On this: INSERT INTO test (value) VALUES('A'); INSERT INTO test (value) VALUES('A') ? the

[sqlite] `UNIQUE` constraint allows multiple NULL: is this expected?

On 1/27/16, Yannick Duch?ne wrote: > On Wed, 27 Jan 2016 14:17:49 -0500 > Igor Tandetnik wrote: > >> On 1/27/2016 2:10 PM, Yannick Duch?ne wrote: >> > `UNIQUE` constraint allows multiple NULL: is this expected? >> >> http://www.sqlite.org/lang_createtable.html >> "For the purposes of UNIQUE

[sqlite] `UNIQUE` constraint allows multiple NULL: is this expected?

On 1/27/2016 2:10 PM, Yannick Duch?ne wrote: > `UNIQUE` constraint allows multiple NULL: is this expected? http://www.sqlite.org/lang_createtable.html "For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs." I'm reasonably, but

[sqlite] UNIQUE constraint failed: ip_addr.pid

On 2015-09-14 09:04 PM, Petr L?z?ovsk? wrote: > Have following table: > > CREATE TABLE ip_addr > ( > /*! Person identificator (PID) %%a */ pid INTEGER PRIMARY KEY NOT NULL, > /*! IP address %%b */ ip_addr VARCHAR (16) NOT NULL, > /*! Status: 0 - Allowed, Unassigned to specific customer (blocked)

[sqlite] UNIQUE constraint failed: ip_addr.pid

Thay track all of us and "optimalise" search results. My list of links could differ from yours. I have googled key phrase '"UNIQUE constraint failed:" sqlite primary key' and similar, but not read whole received documents, perform only brief look on it. This is best practice by my experience,

[sqlite] UNIQUE constraint failed: ip_addr.pid

Hi Petr, if you Google for "database table primary key" the first few results lead to quite good explanations. Also, the english wikipedia's article "Unique key" explains primary keys. HTH Martin Am 14.09.2015 22:25 schrieb Petr L?z?ovsk? : > > I had googled to verify such idea before, but

[sqlite] UNIQUE constraint failed: ip_addr.pid

I had googled to verify such idea before, but have no luck. Thanks, L. > Hello Petr, > defining the column pid as INTEGER PRIMARY KEY you added an implicit > contraint; a primary key means that only one record with a given value > of pid can exist in the table. > See

[sqlite] UNIQUE constraint failed: ip_addr.pid

Hello Petr, defining the column pid as INTEGER PRIMARY KEY you added an implicit contraint; a primary key means that only one record with a given value of pid can exist in the table. See https://www.sqlite.org/lang_createtable.html#rowid Martin Am 14.09.2015 um 21:04 schrieb Petr L?z?ovsk?: >

[sqlite] UNIQUE constraint failed: ip_addr.pid

Have following table: CREATE TABLE ip_addr ( /*! Person identificator (PID) %%a */ pid INTEGER PRIMARY KEY NOT NULL, /*! IP address %%b */ ip_addr VARCHAR (16) NOT NULL, /*! Status: 0 - Allowed, Unassigned to specific customer (blocked) */ /*! Status: 1 - Allowed, Asigned to concrete customer */

Re: [sqlite] UNIQUE constraint

2011/10/23 Simon Slavin > > In this example, the indexed column is a text column. The text fields > could have been very long, and checking long text fields for uniqueness can > involve comparing every byte. Nevertheless, I do not understand the results > you quoted. I

Re: [sqlite] UNIQUE constraint

On 23 Oct 2011, at 3:49pm, Fabian wrote: > So the only overhead for UNIQUE is that extra check? [snip] Right. When doing an INSERT or UPDATE, it checks to see whether the value it's trying to add to the index already exists in the index. If it does, the result is an error. There is no

Re: [sqlite] UNIQUE constraint

> > > No, a UNIQUE index and a regular index are implemented the exact same way. > It's just that, at INSERT and UPDATE time, after finding a proper place to > insert the new value, an additional check is made that the place isn't > already occupied. > So the only overhead for UNIQUE is that

Re: [sqlite] UNIQUE constraint

Fabian wrote: > I have a column with a normal INDEX, and I would like to turn it into an > UNIQUE index, but I'm a bit worried about the performance implications for > inserts. Can someone give some insight into how UNIQUE is implemented in > SQLite, does it create extra

[sqlite] UNIQUE constraint

I have a column with a normal INDEX, and I would like to turn it into an UNIQUE index, but I'm a bit worried about the performance implications for inserts. Can someone give some insight into how UNIQUE is implemented in SQLite, does it create extra tables compared to a normale index, are there

Re: [sqlite] UNIQUE constraint on column

"A. Pagaltzis" <[EMAIL PROTECTED]> wrote: > It's a pity that INSERT OR IGNORE (apparently?) does not set > last_insert_id properly regardless of outcome, Consider this case: CREATE TABLE t1(x INTEGER PRIMARY KEY, y UNIQUE, z UNIQUE); INSERT INTO t1 VALUES(1,'a','b'); INSERT INTO t1

Re: [sqlite] UNIQUE constraint on column

"Shane Harrelson" <[EMAIL PROTECTED]> wrote: > > I have two tables, an "Objects" table with a foreign key into a second > "Strings" table which is composed of unique values. It is a many to > one relationship, that is, several Objects may reference the same > String. When an Object is added,

Re: [sqlite] UNIQUE constraint on column

"Shane Harrelson" <[EMAIL PROTECTED]> wrote: > > I have two tables, an "Objects" table with a foreign key into a second > "Strings" table which is composed of unique values. It is a many to > one relationship, that is, several Objects may reference the same > String. When an Object is added,

Re: [sqlite] UNIQUE constraint on column

On Wed, Jan 31, 2007 at 05:23:29PM -0500, Shane Harrelson wrote: > > I have two tables, an "Objects" table with a foreign key into a second > "Strings" table which is composed of unique values. It is a many to > one relationship, that is, several Objects may reference the same > String. When

Re: [sqlite] UNIQUE constraint on column

On 1/31/07, Dennis Cote <[EMAIL PROTECTED]> wrote: Shane Harrelson wrote: > On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> >> The official way to find the conflicting entry is to do a query. >> >> SELECT rowid FROM table WHERE uniquecolumn=?; >> >> > > Thank you for the reply. I

Re: [sqlite] UNIQUE constraint on column

Hi Shane, On Wed, 31 Jan 2007 09:29:24 -0500, you wrote: >On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> "Shane Harrelson" <[EMAIL PROTECTED]> wrote: >> > when i try to insert a row into a table that has a UNIQUE constraint >> > on a column, and I get the SQLITE_CONSTRAINT result

Re: [sqlite] UNIQUE constraint on column

Shane Harrelson wrote: On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: The official way to find the conflicting entry is to do a query. SELECT rowid FROM table WHERE uniquecolumn=?; Thank you for the reply. I assumed this was most likely the case, and as I said in my original

Re: [sqlite] UNIQUE constraint on column

On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Shane Harrelson" <[EMAIL PROTECTED]> wrote: > when i try to insert a row into a table that has a UNIQUE constraint > on a column, and I get the SQLITE_CONSTRAINT result code because i'm > inserting a duplicate value, is there anyway to

Re: [sqlite] UNIQUE constraint on column

"Shane Harrelson" <[EMAIL PROTECTED]> wrote: > when i try to insert a row into a table that has a UNIQUE constraint > on a column, and I get the SQLITE_CONSTRAINT result code because i'm > inserting a duplicate value, is there anyway to determine the rowid of > the conflict? > > looking at the

[sqlite] UNIQUE constraint on column

when i try to insert a row into a table that has a UNIQUE constraint on a column, and I get the SQLITE_CONSTRAINT result code because i'm inserting a duplicate value, is there anyway to determine the rowid of the conflict? looking at the internals of the VDBE, i found that the rowid of the

[sqlite] UNIQUE constraint on column

when i try to insert a row into a table that has a UNIQUE constraint on a column, and I get the SQLITE_CONSTRAINT result code because i'm inserting a duplicate value, is there anyway to determine the rowid of the conflict? looking at the internals of the VDBE, i found that the rowid of the

Re: [sqlite] UNIQUE Constraint but case sensitive

> Hi, > > I need to create a unique constraint on a column of type varchar, but it is > not case sensitive by default. > Does any one know how to make a unique constraint case sensitive? > CREATE TABLE abc(a UNIQUE COLLATE NOCASE); Bear in mind that the "NOCASE" collation sequence only

[sqlite] UNIQUE Constraint but case sensitive

Hi, I need to create a unique constraint on a column of type varchar, but it is not case sensitive by default. Does any one know how to make a unique constraint case sensitive? Thanks and best regards, Ajay Sonawane