Igor Tandetnik wrote:
> Brandon Pimenta <brandonskypime...@gmail.com> wrote:
>> CREATE TABLE test (
>> test_1 INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT
>> );
>
> Make it
>
> INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
>
> Though NOT NULL is redundant - PRIMARY KEY implies it.
Unlike other sql dialects, PRIMARY KEY in sqlite does not imply NOT NULL
constraint (see documentation).
However, INTEGER PRIMARY KEY is very special and unlike other column types - it
enforce type check and attempt to insert NULL will insert autoincremented value
instead.
sqlite> create table x(a text primary key);
sqlite> create table y(b integer primary key);
sqlite> insert into x values (NULL);
sqlite> insert into x values ('a');
sqlite> insert into x values (3);
sqlite> insert into x values (NULL);
sqlite> insert into y values (NULL);
sqlite> insert into y values ('a');
Error: datatype mismatch
sqlite> insert into y values (3);
sqlite> insert into y values (NULL);
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE x(a text primary key);
INSERT INTO "x" VALUES(NULL);
INSERT INTO "x" VALUES('a');
INSERT INTO "x" VALUES('3');
INSERT INTO "x" VALUES(NULL);
CREATE TABLE y(b integer primary key);
INSERT INTO "y" VALUES(1);
INSERT INTO "y" VALUES(3);
INSERT INTO "y" VALUES(4);
COMMIT;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
