On April 16, 2012 09:27:06 PDT, "Mr. Puneet Kishor" wrote:
Given
CREATE TABLE t (
id INTEGER NOT NULL,
created_on DATETIME DEFAULT CURRENT_TIMESTAMP
PRIMARY KEY (id, created_on)
);
how can I make just the 'id' column auto-increment?
Here is an example of how to get the functionality you're looking for
using SQLite3:
$ sqlite3 autoincrement-compound-key.sq3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT SQLITE_SOURCE_ID();
2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669
sqlite> .schema
CREATE TABLE example (
id INTEGER UNIQUE NOT NULL,
name TEXT,
PRIMARY KEY (id, name)
);
CREATE TRIGGER example_autoincrement BEFORE INSERT ON example
FOR EACH ROW WHEN NEW.id IS NULL BEGIN
INSERT INTO example VALUES (
COALESCE((SELECT MAX(id) FROM example),0) + 1,
NEW.name
);
SELECT RAISE(IGNORE);
END;
sqlite> DELETE FROM example;
sqlite> INSERT INTO example (name) VALUES ('first'),('second'),
('third');
sqlite> INSERT INTO example VALUES (10, 'ten');
sqlite> INSERT INTO example (name) VALUES ('eleven');
sqlite> SELECT * FROM example ORDER BY id;
1|first
2|second
3|third
10|ten
11|eleven
sqlite> .quit
$
The idea is to have the BEFORE INSERT trigger do the actual
computation of the next auto increment value to use (but only when one
was not already specified) AND the actual insertion of the NEW row and
then use the special SQLite3 RAISE(IGNORE) functionality to skip the
insertion of the NEW row that would normally have occurred immediately
following the BEFORE INSERT trigger.
In this example, the id column is constrained to be UNIQUE which
causes an index to be created that the SELECT MAX(id) expression in
the trigger then uses for efficiency. If you do not require the id
column to be UNIQUE, and instead only require that if id is not set,
the max(id) + 1 value is used, you can remove the UNIQUE constraint
and then add a separate CREATE INDEX for the id column so that the
SELECT MAX(id) expression remains efficient in the context of a large
number of rows.
--Kyle
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
