insert: how to force application to provide value for int primary key?

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

insert: how to force application to provide value for int primary key?

Marek Wieckowski
Hi,

I have an application where a table has an unique not null int field which
identifies the records. I would like to use this column as a primary key.

However, the value has a meaning from the application point of view. There
should never be a record inserted into this table with some default
(random/autoincrement) value filled in automatically by the database.


Is there a way in sqlite to ensure (on the database side) that all inserts
for such a table have to have explicit values for such a column?

Example:
    CREATE TABLE example_table
    (
        id INTEGER PRIMARY KEY,
        description TEXT NOT NULL
    );
I want
    INSERT INTO example_table (description) VALUES ('abc');
to fail with an error, and
    INSERT INTO example_table (id, description) VALUES (5, 'abc');
to succeed.

Is it at all possible?

For example, defining a trigger before insert and checking that NEW.id IS
NULL does not work - in before trigger the row has already the field value
(automatically) filled in.

PS: If it's not possible I will end up with a table like
    CREATE TABLE example_table
    (
        id INTEGER PRIMARY KEY,
        value INTEGER UNIQUE NOT NULL,
        description TEXT NOT NULL
    );
with and value being meaningful from the application point of view and id
being internal/artificial. But: I would rather avoid such an additional
artificial column...

Best,
Marek
_______________________________________________
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: insert: how to force application to provide value for int primary key?

Richard Hipp-3
On 9/10/19, Marek Wieckowski <[hidden email]> wrote:
>
> Is there a way in sqlite to ensure (on the database side) that all inserts
> for such a table have to have explicit values for such a column?

Make it a WITHOUT ROWID table:

  CREATE TABLE example_table   (
       id INTEGER PRIMARY KEY,
       description TEXT NOT NULL
   ) WITHOUT ROWID;

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: insert: how to force application to provide value for int primary key?

Marek Wieckowski
Yes, indeed works. Great, thank you!

Marek


On Tue, Sep 10, 2019 at 9:13 AM Richard Hipp <[hidden email]> wrote:

> On 9/10/19, Marek Wieckowski <[hidden email]> wrote:
> >
> > Is there a way in sqlite to ensure (on the database side) that all
> inserts
> > for such a table have to have explicit values for such a column?
>
> Make it a WITHOUT ROWID table:
>
>   CREATE TABLE example_table   (
>        id INTEGER PRIMARY KEY,
>        description TEXT NOT NULL
>    ) WITHOUT ROWID;
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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: insert: how to force application to provide value for int primary key?

Jose Isaias Cabrera-4

Marek Wieckowski, on Tuesday, September 10, 2019 08:08 AM, wrote...

>
> Yes, indeed works. Great, thank you!
>
> Marek
>
>
> On Tue, Sep 10, 2019 at 9:13 AM Richard Hipp, on
>
> > On 9/10/19, Marek Wieckowski, on
> > >
> > > Is there a way in sqlite to ensure (on the database side) that all
> > inserts
> > > for such a table have to have explicit values for such a column?
> >
> > Make it a WITHOUT ROWID table:
> >
> >   CREATE TABLE example_table   (
> >        id INTEGER PRIMARY KEY,
> >        description TEXT NOT NULL
> >    ) WITHOUT ROWID;

Will someone point me to a spot where I can understand this piece of SQL?  Or, a simple explanation would do.  Thanks.

josé
_______________________________________________
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: insert: how to force application to provide value for int primary key?

Dominique Devienne
On Tue, Sep 10, 2019 at 2:20 PM Jose Isaias Cabrera <[hidden email]>
wrote:

> Marek Wieckowski, on Tuesday, September 10, 2019 08:08 AM, wrote...
> > > Make it a WITHOUT ROWID table:
> > >
> > >   CREATE TABLE example_table   (
> > >        id INTEGER PRIMARY KEY,
> > >        description TEXT NOT NULL
> > >    ) WITHOUT ROWID;
>
> Will someone point me to a spot where I can understand this piece of SQL?
> Or, a simple explanation would do.  Thanks.
>

https://www.sqlite.org/withoutrowid.html
_______________________________________________
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: insert: how to force application to provide value for int primary key?

Dominique Devienne
In reply to this post by Marek Wieckowski
On Tue, Sep 10, 2019 at 2:09 PM Marek Wieckowski <[hidden email]> wrote:

> Yes, indeed works. Great, thank you!
>

Note though that it has performance implications perhaps.

This changes to physical structure of the table, to be stored as an index
basically.

So if you do lots of insertions "in the middle", you could have "write
amplifications".
WITHOUT ROWID tables are ideal for read-mostly tables, and function as-if
the
table is an index that covers all columns. So there's no need for separate
IO for the
index (to lookup a PK) then to its table (to lookup the actual row), it's
just one IO.

It's no different from updating an index, except indexes are typically
"skinnier" (have
fewer and smaller columns) than their corresponding tables, limiting the
impact.

So "fat" tables, with large rows, and lost of inserts/updates, is basically
the worse case
scenario for such WITHOUT ROWID tables. It works, no issue there, and as
typical of
SQLite is often fast enough for most DB sizes, but it *can* matter. Just be
aware of it.

My $0.02. --DD

https://www.sqlite.org/withoutrowid.html
_______________________________________________
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: insert: how to force application to provide value for int primary key?

Jose Isaias Cabrera-4
In reply to this post by Dominique Devienne

Dominique Devienne, on Tuesday, September 10, 2019 08:21 AM, wrote...

>
> On Tue, Sep 10, 2019 at 2:20 PM Jose Isaias Cabrera, on
> wrote:
>
> > Marek Wieckowski, on Tuesday, September 10, 2019 08:08 AM, wrote...
> > > > Make it a WITHOUT ROWID table:
> > > >
> > > >   CREATE TABLE example_table   (
> > > >        id INTEGER PRIMARY KEY,
> > > >        description TEXT NOT NULL
> > > >    ) WITHOUT ROWID;
> >
> > Will someone point me to a spot where I can understand this piece of SQL?
> > Or, a simple explanation would do.  Thanks.
> >
>
> https://www.sqlite.org/withoutrowid.html

Thanks, Dominique.  It was that easy. :-)

josé
_______________________________________________
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: insert: how to force application to provide value for int primary key?

R Smith-2
In reply to this post by Dominique Devienne

On 2019/09/10 2:28 PM, Dominique Devienne wrote:

> On Tue, Sep 10, 2019 at 2:09 PM Marek Wieckowski <[hidden email]> wrote:
>
>> Yes, indeed works. Great, thank you!
>>
> Note though that it has performance implications perhaps.
>
> This changes to physical structure of the table, to be stored as an index
> basically.
>
> So if you do lots of insertions "in the middle", you could have "write
> amplifications".
> WITHOUT ROWID tables are ideal for read-mostly tables, and function as-if
> the
> table is an index that covers all columns. So there's no need for separate
> IO for the
> index (to lookup a PK) then to its table (to lookup the actual row), it's
> just one IO.
>
> It's no different from updating an index, except indexes are typically
> "skinnier" (have
> fewer and smaller columns) than their corresponding tables, limiting the
> impact.


I feel like there's some concepts here that are not perfectly defined in
the above statements, or I am mistaken, so please allow me to add:

Any SQLite table is essentially itself a covering Index with mostly
rowid as the indexed key, but in WITHOUT_ROWID tables, it takes whatever
other primary key is offered as the indexed key (not specifying a PK is
an error). There is no other difference, and no difference in
computation and typically a net speed/size gain if your Primary Key is
not exactly INTEGER. (This is the entire point of offering the
WITHOUT_ROWID optimization).

I do not think it is "better" suited to specifically reads or writes -
both can work better (depending on your suitability definition for
"better"). It is however better suited to smaller data widths in the
columns (especially for blobs).

If the above is not true, someone kindly point out to me the truth and
the why of it - thanks!


>
> So "fat" tables, with large rows, and lost of inserts/updates, is basically
> the worse case
> scenario for such WITHOUT ROWID tables. It works, no issue there, and as
> typical of
> SQLite is often fast enough for most DB sizes, but it *can* matter. Just be
> aware of it.


That is interesting - could you elaborate on how exactly lots of inserts
would be worse in WITHOUT_ROWID tables than in normal tables?*
Or point me to documentation or even a dev explaining it in a forum
post, because I have definitely missed that very important bit of info. 
The larger column data thing I do get.  I really need to know how it
"can" matter, because I use this setup near everywhere and so far been
blissfully under the impression that it's never worse, and possibly a
little better for non-integer key use-cases.


*[I get that a text key might have slower insertion into a B-Tree than
an integer key might have into a B*-Tree, but if your PK needs to be
text anyway, I don't see how adding it in a normal table (along with the
normal row_id) could have better performance, unless I'm missing
something important.]


>
> My $0.02. --DD

_______________________________________________
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: insert: how to force application to provide value for int primary key?

Dominique Devienne
On Tue, Sep 10, 2019 at 4:32 PM R Smith <[hidden email]> wrote:

> > So "fat" tables, with large rows, and lost of inserts/updates, is
> basically
> > the worse case
> > scenario for such WITHOUT ROWID tables. It works, no issue there, and as
> > typical of
> > SQLite is often fast enough for most DB sizes, but it *can* matter. Just
> be
> > aware of it.
>
> That is interesting - could you elaborate on how exactly lots of inserts
> would be worse in WITHOUT_ROWID tables than in normal tables?*
>

WITHOUT ROWID tables have a "real" natural key, which as such is much
more likely to have a random distribution, resulting in splicing new rows
all
over the place. While regular tables typically have an auto-incrementing
ROWID,
acting as the B-Tree key, which means new rows are mostly inserted "at the
end",
in the last page, yielding fewer non-leaf page rewrites I'm guessing.

You're probably right to challenge what I wrote. It's mostly intuition, not
hard-facts,
so I could well be completely off-base. I may also have read the above when
I looked
into Oracle IOTs (Index Organized Tables), which are similar I think
(again, perhaps
I'm wrong).

I guess one would need to run experiments with a shim VFS to track IO to
verify
my claims above :). For now, just take it with a grain of salt or just
plain assume
it was talking out of my a..! --DD
_______________________________________________
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: [EXTERNAL] Re: insert: how to force application to provide value for int primary key?

Hick Gunter
BTrees as per concept are aware of sorted load vs random load and will adjust their node splitting algorithm accordingly (e.g. 90/10 split for ordered and 50/50 for random load). The rationale being that an ordered load tends to indicate that new data is unlikely or added at the end, whereas a random load suggests that additional data will be added with in-between keys.

So it really depends on the order of adding records more than the presence or absence of a rowid.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Dominique Devienne
Gesendet: Dienstag, 10. September 2019 16:50
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] insert: how to force application to provide value for int primary key?

On Tue, Sep 10, 2019 at 4:32 PM R Smith <[hidden email]> wrote:

> > So "fat" tables, with large rows, and lost of inserts/updates, is
> basically
> > the worse case
> > scenario for such WITHOUT ROWID tables. It works, no issue there,
> > and as typical of SQLite is often fast enough for most DB sizes, but
> > it *can* matter. Just
> be
> > aware of it.
>
> That is interesting - could you elaborate on how exactly lots of
> inserts would be worse in WITHOUT_ROWID tables than in normal tables?*
>

WITHOUT ROWID tables have a "real" natural key, which as such is much more likely to have a random distribution, resulting in splicing new rows all over the place. While regular tables typically have an auto-incrementing ROWID, acting as the B-Tree key, which means new rows are mostly inserted "at the end", in the last page, yielding fewer non-leaf page rewrites I'm guessing.

You're probably right to challenge what I wrote. It's mostly intuition, not hard-facts, so I could well be completely off-base. I may also have read the above when I looked into Oracle IOTs (Index Organized Tables), which are similar I think (again, perhaps I'm wrong).

I guess one would need to run experiments with a shim VFS to track IO to verify my claims above :). For now, just take it with a grain of salt or just plain assume it was talking out of my a..! --DD _______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: [EXTERNAL] Re: insert: how to force application to provide value for int primary key?

Dominique Devienne
On Tue, Sep 10, 2019 at 5:07 PM Hick Gunter <[hidden email]> wrote:

> So it really depends on the order of adding records more than the presence
> or absence of a rowid.
>

True. I'm making the conjecture that w/ rowid tables tend to be ordered
(via implicit or explicit integer auto-increment rowids), while w/o rowid
tables
tend to be on the random load side. Thanks for your insights. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users