On 16 Apr 2012, at 5:27pm, "Mr. Puneet Kishor" <[email protected]> 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?
If there was a syntax it would be
CREATE TABLE t (
id INTEGER AUTOINCREMENT,
created_on DATETIME DEFAULT CURRENT_TIMESTAMP
PRIMARY KEY (id, created_on)
);
so try that. But the diagram on
http://www.sqlite.org/lang_createtable.html
suggests that AUTOINCREMENT can be used only as part of the PRIMARY KEY
definition.
Another way to do it might be to use a TRIGGER to look up the current MAX()
value of the column and add 1 to it. (I believe you can't do this as a
DEFAULT.)
So you'd define a TRIGGER on INSERT which looked to see if new.id is NULL and
if it is, sets new.id to max(id)+1 . I have no idea whether this would
actually work.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
