Dear SQLiters,
I would like to use INTEGER PRIMARY KEY, but I would like to disable its
implicit AUTOINCREMENT feature. Namely, if INSERT specifies value of the
column, I would like uniqueness to be enforced, but if NULL is supplied, I
would like the operation to fail instead of advancing key to a new integer.
Here is my failed attempt:
CREATE TABLE qqq (
id INTEGER PRIMARY KEY
);
CREATE TRIGGER qqqIdTrigger BEFORE INSERT ON qqq WHEN new.id IS NULL
BEGIN
select RAISE (FAIL, roma);
END;
INSERT INTO qqq (id) VALUES (20);
INSERT INTO qqq (id) VALUES (NULL);
I tried typeof(new.id)='null' in the WHEN clause, both triggers register, but
do not raise fail. Triggering when id is too high (WHEN new.id >20) works. I
thought that when id being inserted is NULL it is autoincremented before
trigger is called. But then, it must trigger the too-high version when passing
threshold --- it did not.
I could replace INTEGER primary key by TEXT primary key and auto increment will
go away. How can I keep integer?
Thank you,
Roman
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
