[EMAIL PROTECTED] wrote:
Hi, i have several databases with same structure on several hosts, and
want them to generate globaly unique ids.
So can I add row to table with explicit id and without modyfing rowid sequence?
eg
insert into db (value) values ( 'first' ) ;
insert into db (value) values ( 'second' ) ;
insert into db (id, value) values ( 500, 'third' ) -- EXPLICIT;
insert into db (value) values ( 'ffourth' ) ;
woud give:
1, first
2, second
500, third
3, fourth
Marek,
I assume that when you are adding the row with an explicit id you are
inserting a row that was first created in one of the other databases and
hence assigned an id there. I.e. that your are merging the databases at
some point.
Autoincrement keys always assign an id one greater than the largest in
table, so they won't work for your application. You will need to use a
probabilistic approach as suggested by Richard, or manage the ids
yourself if you want deterministic ids.
If you create a sequence table with a single row in each database and
initialize the sequence number to a different value for each database,
then you can assign ids that are unique across all the databases. If you
expect a maximum of 1 million records for each database, then the
initial sequence values should be at least 1 million apart.
create table sequence (number integer);
insert into sequence(number) values(1); -- in database 0
insert into sequence(number) values(1000001); -- in database 1
insert into sequence(number) values(2000001); -- in database 2
You are effectively encoding the database number where the record was
first created in the high digits of the id.
Now each local record insert is done with a transaction that inserts the
row using the next id from the sequence and increments sequence number.
begin;
insert into db(id, value) values ((select number from sequence), 'first');
update sequence set number = number + 1;
commit;
When you merge records from another database, you can simply insert them
because they will have unique ids and won't affect the sequence number
used for local inserts.
insert into db(id, value) select id, value from database2.db;
HTH
Dennis Cote
