Hi list,
this question is about the AUTOINCREMENT usage in SQLite when modelling weak
entities. Suppose there are two tables 'Building' and 'Room':
CREATE TABLE Building (
buildingID INTEGER PRIMARY KEY AUTOINCREMENT,
...
);
CREATE TABLE Room (
roomID INTEGER,
buildingID INTEGER CONSTRAINT fk_room_building_id REFERENCES
Building(buildingID),
PRIMARY KEY(roomID, buildingID)
);
CREATE TRIGGER statements follow to enforce the foreign key constraint.
Naturally a room cannot be identified without a building so it is a weak
entity (this seems to be the canonical example in all database books I've
seen).
I'd like to auto-increment the ID numbers in both tables but as far as I can
see the AUTOINCREMENT keyword only works the way it is used in the Building
table. That is, only "INTEGER PRIMARY KEY AUTOINCREMENT" is syntactically
correct.
In the Room table the primary key is a combination of two attributes. How
could I auto-increment the roomID? "roomID INTEGER AUTOINCREMENT" is
syntactically incorrect and the AUTOINCREMENT keyword seems to cannot appear
anywhere else.
What is the SQLite-way to auto-increment when the AUTOINCREMENT keyword is not
available?
Pointers to relevant sections of the FM are most welcome.
Thanks!
Andreas
--
Andreas Ntaflos
Vienna, Austria
GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
