Re: [sqlite] autoincrement and primary key
Thank you, INTEGER -> INT solved the problem. According to manual, this will make search slower, but I give data integrity more weight for now. Roman From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [i...@tandetnik.org] Sent: Monday, May 20, 2013 4:41 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] autoincrement and primary key On 5/20/2013 4:17 PM, Roman Fleysher wrote: > 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. Just make the column "INT PRIMARY KEY NOT NULL" (note INT rather than INTEGER). This way, it is not an alias for ROWID but a column in its own right, and doesn't get assigned a value automatically. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] autoincrement and primary key
On 5/20/2013 4:17 PM, Roman Fleysher wrote: 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. Just make the column "INT PRIMARY KEY NOT NULL" (note INT rather than INTEGER). This way, it is not an alias for ROWID but a column in its own right, and doesn't get assigned a value automatically. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] autoincrement and primary key
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. Switching declared type from INTEGER to INT should do what you want: CREATE TABLE qqq ( id INT PRIMARY KEY ); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] autoincrement and primary key
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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
