PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

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

PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

Digital Dog
Hi! Welcome to the list.

I'm reading documentation on the topic:

"In an elegant system, all tables would behave as WITHOUT ROWID tables even
without the WITHOUT ROWID keyword".
then
"However, NOT NULL was not enforced on PRIMARY KEY columns by early
versions of SQLite due to a bug. By the time that this bug was discovered,
so many SQLite databases were already in circulation that the decision was
made not to fix this bug for fear of breaking compatibility."
at https://www.sqlite.org/withoutrowid.html

I suggest a possible solution to make it more elegant and more compliant.
There could be PRAGMA directives to opt-in for compliant behavior:

PRAGMA default_without_rowid = on
To make all tables created while the directive is in use the WITHOUT ROWID
tables.

PRAGMA enforce_not_null_on_primary_key = on
For WITHOUT ROWID tables it would be a no-op, but for rowid tables it would
restore correct behavior.

I think these changes would not add a lot of code to sqlite and will make
life easier for users.

In spirit it would be similar to already existing
PRAGMA foreign_keys = on

(I also wanted to suggest the same to be able to select behaviour of NULL
values in unique index, but am too shy to do it).

Just a thought.

Thanks!
_______________________________________________
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: PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

E.Pasma
Digital Dog wrote:
>
> PRAGMA default_without_rowid = on
> To make all tables created while the directive is in use the WITHOUT ROWID
> tables.
..

>
> PRAGMA enforce_not_null_on_primary_key = on
> For WITHOUT ROWID tables it would be a no-op, but for rowid tables it would
> restore correct behavior.


Hello, just a remark about this second pragma.
Enforce_not_null_on+primary_key seems obsolete to me, also for "rowid" tables, that do not have a primary key defined.
Because once the first suggested pragma (default_without_rowid) is set, this rowid will be treated as a true integer primary key, including the NOT NULL property. And this also applies if a table is defined with INTEGER PRIMARY KEY as an alias for roiwid.
Does that leave only the first suggestion?

Thanks, E. Pasma

_______________________________________________
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: PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

Digital Dog
> From: E.Pasma <[hidden email]>

> Digital Dog wrote:
> >
> > PRAGMA default_without_rowid = on
> > To make all tables created while the directive is in use the WITHOUT
ROWID
> > tables.
> ..

> >
> > PRAGMA enforce_not_null_on_primary_key = on
> > For WITHOUT ROWID tables it would be a no-op, but for rowid tables it
would
> > restore correct behavior.

> Hello, just a remark about this second pragma.
> Enforce_not_null_on+primary_key seems obsolete to me, also for "rowid"
tables, that do not
> have a primary key defined.
> Because once the first suggested pragma (default_without_rowid) is set,
this rowid will be
> treated as a true integer primary key, including the NOT NULL property.
And this also applies
> if a table is defined with INTEGER PRIMARY KEY as an alias for roiwid.
> Does that leave only the first suggestion?

I may have phrased the subject in a wrong way which misguided you.

My idea is to be able to apply these pragmas separately, I mean a user can
opt-in to using just the second PRAGMA because for a valid reason rowid
tables are better choice for their database. This pragma would make
SQLite's behaviour consistent between rowid and WITHOUT_ROWID tables with
respect to handling primary key values (i.e. failing on NULL).
This setting obviously wouldn't do anything useful to tables without a
primary key. But if in the future SQLite authors implement ALTER TABLE ...
ADD CONSTRAINT ... PRIMARY KEY, then the setting will be taken into account
as well.

default_without_oid is a switch which now would allow changing old default
behaviour - i.e. I decide that I want all subsequently created tables in my
database to be WITHOUT OID without specifying that in the CREATE TABLE
script. It also gives the SQLite authors the ability to change default
value of this setting for new databases in the future, and then the
directive will be the way to restore old/deprecated (by then) way of
creating tables.

Thanks!
_______________________________________________
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: PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

E.Pasma

> 25 nov. 2018, 20:14 Digital Dog wrote:
>
> I may have phrased the subject in a wrong way which misguided you.


Sorry, I was misguided by my limited experience with PRIMARY KEY's. I use to combine these with WITHOUT ROWID. As you referred to and quoted from the documentation, it is only in that case that primary key columns become not null. I see the reason for a separate setting now. Thanks, E. Pasma

btw I like the first suggestion (DEFAULT_WITHOUT_ROWID)

_______________________________________________
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: PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

Richard Hipp-3
In reply to this post by Digital Dog
On 11/23/18, Digital Dog <[hidden email]> wrote:
> I think these changes would not add a lot of code to sqlite and will make
> life easier for users.

SQLite remembers the database schema by storing the original CREATE
TABLE text in the sqlite_master table.  That means the CREATE TABLE is
reparsed whenever a new database connection opens the database file.
This can lead to compatibility problems if the meaning of a CREATE
TABLE statement is somehow affected by PRAGMAs.

--
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: PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

Digital Dog
On Mon, Nov 26, 2018 at 1:19 PM Richard Hipp <[hidden email]> wrote:

> On 11/23/18, Digital Dog <[hidden email]> wrote:
> > I think these changes would not add a lot of code to sqlite and will make
> > life easier for users.
>
> SQLite remembers the database schema by storing the original CREATE
> TABLE text in the sqlite_master table.  That means the CREATE TABLE is
> reparsed whenever a new database connection opens the database file.
> This can lead to compatibility problems if the meaning of a CREATE
> TABLE statement is somehow affected by PRAGMAs.


Thanks for explanation, now I can see it would also interfere with other
features, such as FTS, which creates some tables with rowids, and some
without, and would require adjustments, perhaps even adding new syntax
"WITH ROWID" or a lot of this PRAGMA juggling. At the beginning after
reading the docs I just thought it's worth suggesting fixing the old
behaviour, I'm backing off over this idea.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users