sqlite> CREATE TABLE t(id INTEGER PRIMARY KEY NOT NULL);
sqlite> INSERT INTO t VALUES (NULL); -- No error generated
sqlite> CREATE TABLE tt(id INTEGER PRIMARY KEY CHECK (id IS NOT NULL));
sqlite> INSERT INTO tt VALUES (NULL); -- No error generated either
sqlite> CREATE TRIGGER trg1 BEFORE INSERT ON t FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'foobar 1') WHERE NEW.id IS NULL; END;
sqlite> INSERT INTO t VALUES (NULL); -- Still no error
sqlite> CREATE TRIGGER trg3 BEFORE INSERT ON t FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'foobar 3') WHERE NEW.id = -1; END;
sqlite> INSERT INTO t VALUES (NULL);
Error: foobar 3
-- Huh? Wut???
sqlite> CREATE TABLE ttt(id TEXT PRIMARY KEY NOT NULL);
sqlite> INSERT INTO ttt VALUES (NULL);
Error: ttt.id may not be NULL
sqlite> DROP TABLE ttt;
sqlite> CREATE TABLE ttt(id TEXT PRIMARY KEY CHECK (id IS NOT NULL));
sqlite> INSERT INTO ttt VALUES (NULL);
Error: constraint failed
sqlite> DROP TABLE ttt;
sqlite> CREATE TABLE ttt(id TEXT PRIMARY KEY);
sqlite> CREATE TRIGGER trg4 BEFORE INSERT ON ttt FOR EACH ROW BEGIN
SELECT RAISE(ABORT, 'FOOBAR') WHERE NEW.id IS NULL; END;
Error: FOOBAR
-- So, everything works with non-INTEGER PRIMARY key
I can understand reasons behind accepting NULL for INTEGER PRIMARY KEY (and, a
bit more reluctantly, reasons for accepting NULL for non-INTEGER PRIMARY KEY).
But this does not excuse that explicit constraint does not work (and, erm,
rather funny behavior of trigger).
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
