unique id in more than one table [MySQL]

I want to make auto incremen开发者_C百科t id in two MySQL tables (if in first table there is id=4 then it cannot be id=4 in the second table). My question is how can I do that in the best way?

What you need is an external generated sequence and link it to your 2 tables

You should look at what flickr made, look at this link : http://code.flickr.com/blog/2010/02/08/ticket-servers-distributed-unique-primary-keys-on-the-cheap/

You create the table which generate the id :

CREATE TABLE `Tickets64` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `stub` char(1) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=MyISAM

and to get the new id you make like this :

REPLACE INTO Tickets64 (stub) VALUES ('a');
SELECT LAST_INSERT_ID();

Twitter also recently made something named snowflake, you should look in their github repository

But principally look at what flickr made , its simpler and can be handled easily

Any particular platform?

In some platforms you can set the seed and increment of a sequence - have all even ids in one table, and all odd ids in the other.

The usual objection to this is that there may be gaps, and some people don't like gaps in their surrogate keys. I personally don't put much importance on surrogate key values...

Set the id based on the following query:

SELECT MAX(id) + 1 FROM table1
UNION
SELECT MAX(id) + 1 FROM table2

Should work...

In PostgreSQL, you could do

CREATE SEQUENCE mysequence;

Then for each insert

INSERT INTO mytable values(nextval(mysequence),1,2);

Disclaimer: I'm not a MySQL expert, some syntax can be wrong or rewritten better

You can create a table with only two columns. One for the session_id and another for the sequence_id of type AUTO_INCREMENT. The type for session_id depends on the way you obtain a unique session identifier.

CREATE TABLE sequence 
(
  sequence_id INT NOT NULL AUTO_INCREMENT,
  session_id INT
);

Each time you want to obtain a new value, you can execute an INSERT, a SELECT and, finally, a DELETE:

INSERT INTO sequence (session_id) VALUES (?);
SELECT sequence_id FROM sequence WHERE session_id = ?;
DELETE FROM sequence WHERE session_id = ?;

Another way is to use SAVEPOINT and ROLLBACK TO, but I'm not suer if it's going to work on all MySQL installations:

START TRANSACTION WITH CONSISTENT SNAPSHOT;
SAVEPOINT next_value;
INSERT INTO sequence (session_id) VALUES (?);
SELECT sequence_id FROM sequence WHERE session_id = ?;
ROLLBACK TO next_value;

ok here's the answer - it works and I tested it in MySQL.

create table tab1 (
   pk int not null default 0 primary key,
   col char(1) not null
);

create table tab2 (
   pk int not null default 0 primary key,
   col char(1) not null
);

delimiter |
create trigger tab1_pk before insert on tab1 
   for each row begin 
      set new.pk = greatest(coalesce((select max(pk) from tab1), 0), 
                            coalesce((select max(pk) from tab2), 0)) + 1;
   end;
|

delimiter |
create trigger tab2_pk before insert on tab2
   for each row begin 
      set new.pk = greatest(coalesce((select max(pk) from tab1), 0), 
                            coalesce((select max(pk) from tab2), 0)) + 1;
   end;
|

delimiter ;

As usual with MySQL solutions are a bit convoluted. So here we check the max pk value in both tables (which might be null). The greatest function (a MySQL thing) picks the largest value but results in null if either or both are null. So we coalesce to 0 and add 1. Note the bizzaro delimiter shenanigans. This is necessary because MySQL developers are C / Perl programmers that think all things must end in a semicolon and thus the need for a delimiter in SQL creeped in. The semicolons idiotically terminate the definition of a trigger or stored procedure early unless you change the delimiter to something else - a | in this case.

Why is MySQL so popular? I guess it's like asking why PHP, Perl and Java are too - ugh.

Anyway stepping off of the rant box now so here are the tests:

dbserver/mydb> insert tab1(col) values('a');
Query OK, 1 row affected (0.06 sec)

dbserver/mydb> select * from tab1;
+----+-----+
| pk | col |
+----+-----+
|  1 | a   | 
+----+-----+
1 row in set (0.00 sec)

dbserver/mydb> insert tab1(col) values('b');
Query OK, 1 row affected (0.05 sec)

dbserver/mydb> select * from tab1;
+----+-----+
| pk | col |
+----+-----+
|  1 | a   | 
|  2 | b   | 
+----+-----+
2 rows in set (0.00 sec)

dbserver/mydb> insert tab2(col) values('a');
Query OK, 1 row affected (0.07 sec)

dbserver/mydb> select * from tab2;
+----+-----+
| pk | col |
+----+-----+
|  3 | a   | 
+----+-----+
1 row in set (0.00 sec)

dbserver/mydb> insert tab1(col) values('c');
Query OK, 1 row affected (0.07 sec)

dbserver/mydb> select * from tab1;
+----+-----+
| pk | col |
+----+-----+
|  1 | a   | 
|  2 | b   | 
|  4 | c   | 
+----+-----+
3 rows in set (0.00 sec)

dbserver/mydb> insert tab2(col) values('b');
Query OK, 1 row affected (0.05 sec)

dbserver/mydb> select * from tab2;
+----+-----+
| pk | col |
+----+-----+
|  3 | a   | 
|  5 | b   | 
+----+-----+
2 rows in set (0.00 sec)

If you can accept change the auto_increment increment values for all tables in the server, you can bypass the heavy work of setting triggers or separate tables and whatnot:

create table a (...);
create table b (...);
alter table a set auto_increment=1;
alter table b set auto_increment=2;

set @@auto_increment_increment=2;

After that, you'll have odd IDs in table 'a' and even IDs in table 'b'.

Of course, the down side is that changing this variable affects all auto_increments, so you'll end up with gaps in other tables.