RE: [sqlite] Database Level Unique Sequence
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]
-
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
Re: [sqlite] Database Level Unique Sequence
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]
-
Re: [sqlite] Database Level Unique Sequence
Hello Andre,
I'd probably add a single table that does nothing but, supply
auto-increment values to the other tables. You'd only have
"autoincrement" in this one table then when you need a unique number,
insert into the link table, get the rowid/autoincrement value and then
use this value for the link in the other tables you're adding data
too.
In these other tables, the link value might be "primary key" but, not
"autoincrement".
C
Monday, July 2, 2007, 12:36:02 PM, you wrote:
AdP> Good day everyone.
AdP>
AdP> I would like to know how to create an Autoincrement field and insure
AdP> that it is unique across the database, I tested this and it does not
AdP> seem to work:
AdP>
c:\Temp>>sqlite3 temp.db
AdP> SQLite version 3.3.17
AdP> Enter ".help" for instructions
sqlite>> create table temptable (id integer primary key autoincrement,
AdP> info text)
AdP> ;
sqlite>> create table temptable2 (id integer primary key autoincrement,
AdP> info text
AdP> );
sqlite>> insert into temptable (info) values ('info1');
sqlite>> insert into temptable2 (info) values ('info2');
sqlite>> select * from temptable;
AdP> 1|info1
sqlite>> select * from temptable2;
AdP> 1|info2
sqlite>>
AdP>
AdP> as you can see both have id = 1
AdP>
AdP> I need this because I need a link table that wont know which table the
AdP> id comes from, and I cant add all the fields to make a compound key as
AdP> some of the values would then be blank.
AdP>
AdP> Any suggestions is greatly appreciated.
--
Best regards,
Tegmailto:[EMAIL PROTECTED]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Database Level Unique Sequence
"P Kishor" <[EMAIL PROTECTED]> wrote: > On 7/2/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: > > > > I would like to know how to create an Autoincrement field and insure > > that it is unique across the database, > > 1. Use a GUID, or > 2. Use some kind of crypt/md5 kinda function on a random string (same >effect as above), or > 3. Use a timestamp as a unique ID, esp. in conjunction with a hi-res >time lib, or > > Don't know if #1 and #2 above are built-in, but #3 could probably be > done without any external library. > You can do #1 and/or #2 using the built-in randomblob() function. randomblob(N) returns a blob containing N bytes of good quality randomness (depending on how well the built-in PRNG was seeded). If you want your ID to be human readable, you might use the hex() function to convert the random blob into a hexadecimal string: INSERT INTO table1(id,...) VALUES(hex(randomblob(16)), ...); -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Database Level Unique Sequence
Re: "I would like to know how to create an Autoincrement field and insure that it is unique across the database [not just within one table]...] Hi Andre, Do you mind saying why it is you need the field to be unique across all tables? Wouldn't the fact that a key is unique within its own table be sufficient? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Database Level Unique Sequence
On 7/2/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: 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: .. 1. Use a GUID, or 2. Use some kind of crypt/md5 kinda function on a random string (same effect as above), or 3. Use a timestamp as a unique ID, esp. in conjunction with a hi-res time lib, or Don't know if #1 and #2 above are built-in, but #3 could probably be done without any external library. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ S Policy Fellow, National Academy of Sciences http://www.nas.edu/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
