Thanks to everyone for the suggestions. I have considered most these options,
1. Don't really want to create a hash (md5) or guid, this will be too large. 2. I could create a trigger for the table that as you suggest get's it from one sequence table, but not sure what would be the speed impact. I think I will indeed go with Igor's suggestion, it may indeed offer some advantages in the future. So the link table will be TABLE_ID INTEGER, TABLE_TYPE INTEGER And the primary key is on those two. I might however need to change this approach, and add LINK_ID as a sequence and rather use a unique index on those fields, as I just always feel a one primary key in a table is a better design. But this comes down to one of those things where there are many ways to do it but finding the BEST way to do it. As I said though generator or sequence support for a future version of SQLite would be great to an already great project. Cheers. -----Original Message----- From: Israel Figueroa [mailto:[EMAIL PROTECTED] Sent: 03 July 2007 04:06 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Database Level Unique Sequence I think that what you need is a sequence... but sqlite doesn't implement sequiences. A workarround cuold be to create a table with no data on it.. but used to create a unique index which can be used to insert data on the others tables. To know the id you should use the sqlite3_last_insert_rowid function. that should look like: create table indextable (id integer primary key autoincrement, t text); create table temptable1 (id integer primary key, info text); create table temptable2 (id integer primary key , info text); insert into indextable (t) values ('void'); UNIQUE_ID=sqlite3_last_insert_rowid(); insert into temptable1 (id,info) values (UNIQUE_ID,'info1'); insert into indextable (t) values ('void'); UNIQUE_ID=sqlite3_last_insert_rowid(); insert into temptable2 (id,info) values (UNIQUE_ID,'info2'); it doesn't looks nice... but it should work. You can add a fourth statement where you delete the inserted data in indextable... so you dont have that extra space in the database file. But eventualy it can be used to store some usefull data. 2007/7/2, Clark Christensen <[EMAIL PROTECTED]>: > > http://www.mail-archive.com/sqlite-users@sqlite.org/msg10803.html > > Describes a patch that implements a sequence table, and functions to deal > with it. You could use something like that to implement a > unique-across-all-tables ID scheme. Though I think someone else (Igor?) > already suggested something similar. > > -Clark > > ----- Original Message ---- > From: Andre du Plessis <[EMAIL PROTECTED]> > To: sqlite-users@sqlite.org > Sent: Monday, July 2, 2007 9:36:02 AM > Subject: [sqlite] Database Level Unique Sequence > Good day everyone. > > > > I would like to know how to create an Autoincrement field and insure > that it is unique across the database, I tested this and it does not > seem to work: > > > > c:\Temp>sqlite3 temp.db > > SQLite version 3.3.17 > > Enter ".help" for instructions > > sqlite> create table temptable (id integer primary key autoincrement, > info text) > > ; > > sqlite> create table temptable2 (id integer primary key autoincrement, > info text > > ); > > sqlite> insert into temptable (info) values ('info1'); > > sqlite> insert into temptable2 (info) values ('info2'); > > sqlite> select * from temptable; > > 1|info1 > > sqlite> select * from temptable2; > > 1|info2 > > sqlite> > > > > as you can see both have id = 1 > > > > I need this because I need a link table that wont know which table the > id comes from, and I cant add all the fields to make a compound key as > some of the values would then be blank. > > > > Any suggestions is greatly appreciated. > > > > > > > ------------------------------------------------------------------------ ----- > To unsubscribe, send email to [EMAIL PROTECTED] > > ------------------------------------------------------------------------ ----- > > -- Thanks God ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
