Can I suggest another way that I adopted a long time ago to solve this kind of issue? Forget the autoincrement function included in your RDBM and use a customized one more or less similar to the one I developped in my ZQLib (adapted from ZEOSDBO) for Delphi (previously I did the same thing with MS-Access):
{==Primary key management============ Primary Key is a shaped string which consists of prefix+serial number PkSize is the width of the serial number Const NullString=''; M_ZQLite is a module hosting various ZEOSDBO objects, especially ZQread which is a TZReadOnlyQuery PrimKeyZero, PrimKeyMax and LastPrimKey may be private. } Function PrimKeyZero(PkSize:integer):string; //lowest serial number value var i:integer; begin Result:=NullString; For i:=1 to PkSize Do Result:=Result+'0'; end; Function PrimKeyMax(PkSize:integer):string; //highest serial number value var i:integer; begin Result:=NullString; For i:=1 to PkSize Do Result:=Result+'9'; end; Function LastPrimKey(Table, PkField,Prefix: String;var PkVal:string):boolean; begin Result:=False; With M_ZQLite Do BEGIN ZQread.SQL.Clear; ZQread.SQL.Add('SELECT MAX('+PkField +') FROM '+ Table + ' WHERE '+ PkField + ' LIKE '+QuotedStr(Prefix+'%')+';'); Try ZQread.Open; if ZQread.IsEmpty Then PkVal:=NullString else PkVal:=ZQread.Fields[0].AsString; Result:=True; Finally ZQread.Close; End; END; End; Function NextPrimKey(Table, PkField,Prefix: String;PkSize:integer;var PkNext:string):boolean; var LastK: String; N:longint; code:integer; Begin Result:=False; If not LastPrimKey(Table, PkField, Prefix,LastK) Then exit; If LastK=NullString Then Begin PkNext:=Prefix + PrimKeyZero(PkSize); Result:=True; Exit; End Else LastK := Copy(LastK,Length(Prefix)+1, PkSize); If LastK =PrimKeyMax(PkSize) Then begin ShowMessage(Glob_PrimKeyOver); exit; End; Val(LastK,N,code); PkNext:= Prefix + Format('%*.*d',[PkSize,PkSize,N+1]); Result:=True; End; Assuming that the Civil Work DB has to manage les than100 buildings, each one containing less than 10000 rooms. When adding a building: if NextPrimKey(T_Building, 'BuildingID',NullString,2,NextBuilding) then ZQrun('INSERT INTO T_Building(BuildingID,.....) VALUES('+QuotedStr(NextBuilding)+',;;;;;);'); When adding a room: if NextPrimKey(T_Room, 'RoomID',CurrentBuilding,4,NextRoom) then ZQrun('INSERT INTO T_Room(RoomID,.....) VALUES('+QuotedStr(NextRoom)+',;;;;;);'); I would point out that this approach permits meaningfull keys which offer a lot of possibilities. Igor Tandetnik a écrit : > "Andreas Ntaflos" > <[EMAIL PROTECTED]> wrote in > message news:[EMAIL PROTECTED] > >> On Friday 05 September 2008 23:59:25 Igor Tandetnik wrote: >> >>> Define "weak entity". Precisely what properties does it have that are >>> not represented in the model? >>> >> A weak entity has no possible combination of attributes that would >> uniquely identify it in the relational model. It's existence depends >> on another entity. The building-room-example illustrates this nicely. >> There's nothing to hinder two separate, uniquely identifiable >> buildings to have the same room number scheme, so to identify a >> single room the key of the building is needed in combination with the >> room's numer. >> > > Well, in this case you've violated your "conceptual model" the moment > you decided to assign unique auto-generated numbers to your rooms, in a > way completely divorced from how numbers would be assigned to real rooms > in real buildings. As the room is no longer a "weak entity" by your > definition, of course you have difficulty modeling it as one. Why again > are you surprised? > > >> The property the desired model has that are not represented in this >> model we discuss is that a room's primary key does not contain a >> reference to the primary key of the owning entity. It is globally and >> uniquely identifiable without ever looking at the building. >> > > And that is bad because...? > > >> This is a practical consequence and thus the model you propose is >> possible and probably even feasible, but it is no longer the model I >> wanted to implement. Plus I don't really see why it would be wrong to >> use an auto-incrementing key for the roomID while keeping the tuple >> (roomID, buildingID) as a primary key. >> > > What's the point? What's so special about PRIMARY KEY that you > absolutely must have one? All it does is guarantee uniqueness - but you > already have that. You are getting hung up on the syntax of your CREATE > TABLE statement, rather than on the properties of the underlying > relation. > > Let's put it this way: are there any queries that you can't run against > the current model, but would be able to run against your hypothetical > ideal model? And if there are no such queries, what's the difference > between the two? > > >> It seems that there are two choices available: go with your model, >> which is simpler and certainly more efficient, but loses an aspect of >> the desired model (namely that rooms are by themselves not uniquely >> identifiable). >> > > Again, it's not the model that loses the property - it's your initial > decision of using unique room numbers. After that, you just insist on a > syntactical game of make-believe, for no other reason that to make > yourself feel better, as far as I can tell. You say: I want to assign > unique numbers to rooms, but I want to pretend the rooms are not > uniquely identifiable by those unique numbers. Does this really make > sense to you? > > Igor Tandetnik > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users