On Friday 05 September 2008 22:41:14 Igor Tandetnik wrote: > Andreas Ntaflos > > <[EMAIL PROTECTED]> wrote: > > On Friday 05 September 2008 21:57:30 Igor Tandetnik wrote: > >> Andreas Ntaflos > >> > >>> 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? > > > > 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. > > If you expect rooms in different buildings to have the same ID, then you > can't use autoincrement for it anyway.
I understand now what you mean. Somehow I failed to realise that auto-incrementing (whether using a SERIAL or AUTOINCREMENT) increments an attribute in the table regardless of what it is of which the table is a representation. There would not be any two rooms with the same room number, regardless of the building they are in. > > 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. > > But then you won't have two rooms with the same ID, and there won't be > any problem making roomID a primary key. > > I don't see how you plan to have it both ways. It doesn't make much sense, yes. I am sorry for the confusion. My problem statement remains partially valid, though. Auto-incrementing the roomID in the Room table would make each room have its very own room number but the primary key still would need to consist of both buildingID and roomID or else the modelling of a room as a weak entity wouldn't work correctly. The buildingID is still a foreign key so SELECTing all rooms of one particular building is still possible but that's only part of the conceptual model. I suppose it's not something the authors of books on database systems would approve of. A weak entity's primary key has to include the primary key of its owner entity or else it isn't weak. Yet in the case of SQLite and AUTOINCREMENT this does not seem possible. Anyway, thanks for opening my eyes to the obvious :) Andreas -- 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
