On Friday 05 September 2008 21:57:30 Igor Tandetnik wrote: > Andreas Ntaflos > > <[EMAIL PROTECTED]> wrote: > > 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. > > Wait: if you want roomID to be autoincrement, then it's going to be > unique (which, after all, is the point of autoincrement). Run it by my > again: why can't you identify a room without a building?
Thank you for the quick reply! Well a room's existence depends on the building in which it is. Different buildings can use the same room numbering scheme so in order to know which room we are looking at we need to know its owning building. Hence a room is a weak entity. But it seems I misunderstood the point of AUTOINCREMENT. I am looking for something like PostgreSQL's SERIAL data type [1] so when creating new rooms I don't have to manually specify the roomID. Instead the next possible roomID should be chosen automatically when INSERTing. What is the correct SQLite-way of doing this? Thanks, Andreas [1] http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html#DATATYPE-SERIAL -- Andreas Ntaflos Vienna, Austria GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
