Can I assign negative value to the INTEGER PRIMARY KEY column?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Can I assign negative value to the INTEGER PRIMARY KEY column?

John Smith
Hi,


I read in SQLite documentation that if I define column of type INTEGER PRIMARY KEY then this column will become an alias to SQLite internal 64-bit integer index that uniquely identifies the row (hence ‘rowid’).

I also read that the initial default value that will be used for such column is 1.


My question:

I am migrating data from a previous schema to my new schema, which wants to use this INTEGER PRIMARY KEY capability.

The thing is that in my old schema the integer indexes, which needs to be mapped to the new INTEGER PRIMARY KEY column, starts with value -2 (minus two).

The values are unique and are going up, but always start at (-2).

Since my schema contains many relations I do not wish to modify these indexes.

My question – if I copy the indexes values as-is, so some are negative, will my INTEGER PRIMARY KEY column still be an alias to the SQLite internal ‘rowid’ column, or will such values break this alias connection?

Many thanks, Paz
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I assign negative value to the INTEGER PRIMARY KEY column?

J. King-3
On February 11, 2019 8:35:57 AM EST, John Smith <[hidden email]> wrote:

>Hi,
>
>
>I read in SQLite documentation that if I define column of type INTEGER
>PRIMARY KEY then this column will become an alias to SQLite internal
>64-bit integer index that uniquely identifies the row (hence ‘rowid’).
>
>I also read that the initial default value that will be used for such
>column is 1.
>
>
>My question:
>
>I am migrating data from a previous schema to my new schema, which
>wants to use this INTEGER PRIMARY KEY capability.
>
>The thing is that in my old schema the integer indexes, which needs to
>be mapped to the new INTEGER PRIMARY KEY column, starts with value -2
>(minus two).
>
>The values are unique and are going up, but always start at (-2).
>
>Since my schema contains many relations I do not wish to modify these
>indexes.
>
>My question – if I copy the indexes values as-is, so some are negative,
>will my INTEGER PRIMARY KEY column still be an alias to the SQLite
>internal ‘rowid’ column, or will such values break this alias
>connection?
>
>Many thanks, Paz
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Rowids are signed integers and can be negative. The documentation does advise (somewhere...) against using negative rowids because they are larger (when stored) than the typical used range of positive ones, but that's it.
--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I assign negative value to the INTEGER PRIMARY KEY column?

Richard Hipp-3
On 2/11/19, J. King <[hidden email]> wrote:
>
> Rowids are signed integers and can be negative. The documentation does
> advise (somewhere...) against using negative rowids because they are larger
> (when stored) than the typical used range of positive ones, but that's it.

Exactly.  Negative rowids work fine.  It is only that the rowid values
are stored using the "varint" format which uses between 1 and 9 bytes
to store a 64-bit integer.  Small non-negative values like 1 and 2 use
just 1 byte.  But -1 and -2 and all other negative numbers use all 9
bytes.  It is only a disk space compactness issue, and in the OPs case
there are only two rows involved, so the impact will be unmeasurably
small.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users