[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-dev
Subject:    Re: [sqlite-dev] Transition to enforcement of NOT NULL on a PRIMARY KEY
From:       J Decker <d3ck0r () gmail ! com>
Date:       2011-03-23 1:35:50
Message-ID: AANLkTi=Uk-DyydBs=v+MwNygf3GAWGOruNftrnXUJuHi () mail ! gmail ! com
[Download RAW message or body]

On Mon, Mar 21, 2011 at 7:28 AM, D. Richard Hipp <drh@hwaci.com> wrote:
> The SQLite documents says (and has said for a long time) this:
>
> "According to the SQL standard, PRIMARY KEY should always imply NOT NULL.=
 Unfortunately, due to a long-standing coding oversight, this is not the ca=
se in SQLite. Unless the column is an INTEGER PRIMARY KEY SQLite allows NUL=
L values in a PRIMARY KEY column. We could change SQLite to conform to the =
standard (and we might do so in the future), but by the time the oversight =
was discovered, SQLite was in such wide use that we feared breaking legacy =
code if we fixed the problem. So for now we have chosen to continue allowin=
g NULLs in PRIMARY KEY columns. Developers should be aware, however, that w=
e may change SQLite to conform to the SQL standard in future and should des=
ign new programs accordingly. "
>
> I'd like to start enforcing NOT NULL on PRIMARY KEY. =A0The question is, =
how to do this with the least disruption? =A0Ideas:

Can you just enforce it on the create table prepare?  Old tables which
are already created won't have the attribute NOT NULL, otherwise you
could throw a warning indicating NOT NULL is a required attribute on
PRIMARY KEY; otherwise it works exactly the same as any other NOT NULL
column... however, if it's auto_increment, shouldn't I pass NULL (or
0) if I want the next ID?

>
> (1) Only enforce NOT NULL on PRIMARY KEY if SQLite is compiled with SQLIT=
E_PRIMARY_KEY_NOT_NULL. =A0The legacy behavior is preserved by default.
>
> (2) Enforce NOT NULL on PRIMARY KEY by default but revert to legacy behav=
ior if compiled using SQLITE_PRIMARY_KEY_NULL_OK
>
> (3) Provide a start-time option (a new opcode for the sqlite3_config() in=
terface) that will enable or disable enforcement of NOT NULL on a PRIMARY K=
EY for all threads and connections within the process. =A0(3a) Legacy behav=
ior by default. =A0(3b) NOT NULL enforcement by default.
>
> (4) Provide yet-another-pragma to turn enforcement of NOT NULL on PRIMARY=
 KEY on and off. =A0(4a) Legacy behavior by default. =A0(4b) NOT NULL enfor=
cement by default.
>
> What are your opinions on this, gentle readers?
>
> D. Richard Hipp
> drh@hwaci.com
>
>
>
> _______________________________________________
> sqlite-dev mailing list
> sqlite-dev@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev
>

[prev in list] [next in list] [prev in thread] [next in thread]