Re: [sqlite] (python) how to define unchangeable global ID in a table?

On 28 Jun 2010, at 7:31pm, Pavel Ivanov wrote: >> Such freedom is not suitable for data interchange between two systems. Not >> that SQLite or any other database would change the PK during import-export, >> but they are free to do so as long as the *intramural* integrity is >> preserved. > >

Re: [sqlite] (python) how to define unchangeable global ID in a table?

My remarks were made in the context of AUTOINCREMENTING primary keys. With auto-incremented keys, the database is free to implement the incrementation in the manner it sees fit. It may skip numbers. It may re-generate keys on import/restore and cascade the changes out to child tables. Given these

Re: [sqlite] (python) how to define unchangeable global ID in a table?

On Mon, Jun 28, 2010 at 02:15:01PM -0400, Tim Romano scratched on the wall: > Since no SQL standard requires the primary key to do anything other than be > unique within the relation and with respect to its foreign references. As > long as the database maintains meets those requirements, it is

Re: [sqlite] (python) how to define unchangeable global ID in a table?

> Such freedom is not suitable for data interchange between two systems. Not > that SQLite or any other database would change the PK during import-export, >  but they are free to do so as long as the *intramural* integrity is > preserved. Can you point out some documentation supporting this

Re: [sqlite] (python) how to define unchangeable global ID in a table?

Pavel, Although you are right that SQLite persists the rowid for INTEGER PRIMARY KEYS across VACUUMs and suchlike, I too am right. I was focusing on the OP's use of the words "guaranteed" and "globally" and on this requirement: The OP wrote: "BTW, in my story it is necessary to store the unique

Re: [sqlite] (python) how to define unchangeable global ID in a table?

> the primary key column [id] is defined as INTEGER PRMARY KEY; so defined, > SQLite will treat this column as an alias for the ROWID. There is no > guarantee that ROWID will remain constant over time: its job is very simple: > to be unique.  There is no "be constant" clause in its contract, so to

Re: [sqlite] (python) how to define unchangeable global ID in a table?

You could also define your primary key as INT PRIMARY KEY (rather than INTEGER PRIMARY KEY) and in that case SQLite will treat it as a normal column and it will remain immutable over time (unless you change it). However, I would advise against using INT PRIMARY KEY inasmuch as this subtle (yet

Re: [sqlite] (python) how to define unchangeable global ID in a table?

And myspecialvalue can be INTEGER|TEXT. On Mon, Jun 28, 2010 at 8:39 AM, Tim Romano wrote: > In this example: > > CREATE TABLE tableA { > > id INTEGER PRIMARY KEY AUTOINCREMENT, > name TEXT NOT NULL UNIQUE, > myspecialvalue TEXT NOT NULL UNIQUE > } > > > >

Re: [sqlite] (python) how to define unchangeable global ID in a table?

In this example: CREATE TABLE tableA { id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, myspecialvalue TEXT NOT NULL UNIQUE } the primary key column [id] is defined as INTEGER PRMARY KEY; so defined, SQLite will treat this column as an alias for the ROWID. There is no

Re: [sqlite] (python) how to define unchangeable global ID in a table?

On 26 Jun 2010, at 4:34pm, kee wrote: > both of them may have duplicated records ... and later ... >name TEXT NOT NULL UNIQUE, Those two things contradict each-other. If you specify UNIQUE you can't have duplicated values. > CREATE TABLE tableA { Try to get out of that habit. if

[sqlite] (python) how to define unchangeable global ID in a table?

Dear all I have 2 string lists, listA and listB as raw data which need to be store in the SQLITE database, both of them may have duplicated records listA listB === orangejapan pear