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
