Being a database newb I'll defer to you guys, and plan to remove the
unique keyword, but if the definition were "bad" shouldn't it fail to
create the table? Not sure how it could be argued that accepting a
definition which allegedly leads to a problem < 0.01% of the time is an
acceptable sequence.
In any case, this thread seems to indicate that at one point primary key
+ unique resulted in a wasteful doubly indexed, but presumably still
functional, database. But at the end it seems to have been confirmed
that it is optimized out as of 2008...
http://sqlite.1065341.n5.nabble.com/On-UNIQUE-and-PRIMARY-KEY-td41297.html
On 2/13/2017 7:28 PM, Stephen Chrzanowski wrote:
To further Keiths comment, I've never seen a database NOT treat a primary
key as not unique. Primary Key automatically makes the field have a unique
constraint.
On Mon, Feb 13, 2017 at 8:14 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
This is probably due to your bad table definition.
CREATE TABLE messages_priority (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
type TEXT,
json TEXT,
options TEXT,
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
is kind of nonsensical. You are declaring some "data" column to be both a
primary key, and autoincrement, and have a unique index at the same time.
You are probgably trying to declare an AUTOINCREMENT (meaning value
inserted is always bigger than any even inserted before) on a rowid
column. This is pronounced thusly:
id integer primary key autoincrement
Only God (or DRH) knows what is done with the declaration "id INTEGER NOT
NULL PRIMARY KEY AUTOINCREMENT UNIQUE"
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On Behalf Of Jeff B.
Sent: Monday, 13 February, 2017 11:56
To: SQLite mailing list
Subject: Re: [sqlite] Partially "hidden" row, very bizarre state
Should have read up on integrity_check before posting the results... I
re-worked my tool and here is the output:
pragma integrity_check;
*** in database main ***
On tree page 11 cell 0: Rowid 424 out of order
wrong # of entries in index sqlite_autoindex_messages_priority_1
**2 rows returned**
On 2/13/2017 11:58 AM, R Smith wrote:
Could you run and post the output of your tool to the following two
queries please:
pragma integrity_check;
select '<'||id||'>',created from messages_priority order by id limit ;
Thanks,
Ryan
On 2017/02/13 7:32 PM, Jeff B. wrote:
Hi Everyone,
Sorry for the strange subject, but the situation I'm facing is so odd
that I wasn't even sure how to summarize it. First, a little
context: I'm using v1.0.104 of System.Data.SQLite.Core in my C#/.NET
4.5 project. I have a table that is created as follows....//
_______________________________________________
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
_______________________________________________
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
