Michael Schlenker <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> i noticed the note in the documentation that the implicit NOT NULL
> constraint for PRIMARY KEYs is currently not enforced by SQLite and
> wondered if there is an explicit way to set it?
>
> I expected specifying the NOT NULL explicitly would work and enforce it,
> but it does not:
>
> CREATE TABLE foo (a INTEGER NOT NULL, b TEXT, PRIMARY KEY(a) );
>
> Inserting a NULL into column a works fine, this does not raise an error.
>
> INSERT INTO foo VALUES (NULL,'bar');
>
> Is the only workaround creating a trigger that calls RAISE(FAIL,...)?
>
An explicit NOT NULL does work to inforce non-null-ness in primary
keys. Except for an INTEGER PRIMARY KEY, which is a special case.
For INTEGER PRIMARY KEY, an attempt to insert a NULL value automatically
converts the NULL into a new distinct key.
I suppose it would be a reasonable request a change to this so that
an explicit NOT NULL preventing the automatic unique key generation
feature. As with the implicit NOT NULL on PRIMARY KEY, this would
be a subtly incompatible change, though, so would need to be
approached with extreme caution. At a minimum I will need to increase
the second number of the version (3.3.x to 3.4.0) and there will need
to be prominent announcement of the change and community consensus
that the change is worth while.
Note that even using a trigger to try to catch the null insertion
attempt does not work for an INTEGER PRIMARY KEY. The only way
I have found to prevent a NULL insert into an INTEGER PRIMARY KEY
is to create a view on the table and an INSTEAD OF INSERT trigger
on the view and do your inserts through the view:
CREATE TABLE t1(a INTEGER PRIMARY KEY NOT NULL, b);
CREATE VIEW v1 AS SELECT a, b FROM t1;
CREATE TRIGGER r1 INSTEAD OF INSERT ON v1 BEGIN
SELECT raise(fail,'primary key is null') WHERE new.a IS NULL;
INSERT INTO t1 VALUES(new.a, new.b);
END;
INSERT INTO v1 VALUES(NULL,5);
--
D. Richard Hipp <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
