Hi all,
Newbie question here: Imagine I’m writing a chat server with multiple
chat rooms, where each chat room has its own unique identifier. I want
to store the messages from each chat room and allow later retrieval of
all messages in a chat room in their proper order. I was thinking how
to do this with one table (this is an iPhone app so I’m being a little
resource-conscious), and was thinking of something like:
CREATE TABLE Messages (chat_room_id TEXT NOT NULL, message_id INTEGER
NOT NULL, message TEXT NOT NULL, PRIMARY KEY (plan_id, message_id));
What I’d like is to be able to do something like this:
INSERT INTO Messages (chat_room_id, message) VALUES ('chat_room_id1',
'message1');
and have message_id be assigned an auto-incremented value. If this
exact INSERT statement were run again, the first command would create
a row with a message_id of 1 (or whatever starting value it decides),
and the second command would create another row with a message_id of 2
(or whatever successively higher value it decides).
Then to retrieve all messages in the order in which they were
inserted, I could run:
SELECT message FROM Messages WHERE chat_room_id=’chat_room_id1’;
and the messages would automatically be returned in order from oldest
to newest. But the INSERT command fails with:
Error: Messages.message_id may not be NULL
>From the documentation I understand that if an INTEGER as a primary
key by itself is auto-incremented. Is it possible to use
auto-incrementing for message_id in my compound primary key above?
Thanks,
Mike
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
