How to have Unique IDs across two or more tables in MySQL?

Question

I have a table called events where all new information goes. This table works as a reference for all queries for news feed(s) so event items are selected from there and information corresponding to that event is retrieved from the correct tables.

Now, here's my problem. I have E_ID's in the events table which correspond to the ID of an event in a different table, be it T_ID for tracks, S_ID for status and so on... These ID's could be the same so for the time being I just used a different auto_increment value for each table so status started on 500 tracks on 0 etc. Obviously, I don't want to do that as I have no idea yet of which table is going to have the most data in it. I would assume status would quickly exceed tracks.

The information is inserted into the event table with triggers. Here's an example of one;

BEGIN
INSERT INTO events (action, E_ID, ID)
VALUES ('has some news.', NEW.S_ID, NEW.ID);
END

That ones for he status table.

Is there an addition to that trigger I can make to ensure the NEW.S_ID != an E_ID currently in events and if it does change the S_ID accordingly.

Alternatively, is there some kind of key I can use to reference events when auto incrementing the S_ID so that the S_ID is not incremented to a value of E_ID.

Those are my thoughts, I think the latter solution would be better but I doubt it is possible or it is but would require another reference table and would be too complex.

Answer 1

It's really uncommon to require a unique id across tables, but here's a solution that will do it.

/* Create a single table to store unique IDs */
CREATE TABLE object_ids (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    object_type ENUM('event', ...) NOT NULL
) ENGINE=InnoDB;

/* Independent object tables do not auto-increment, and have a FK to the object_ids table */
CREATE TABLE events (
    id INT UNSIGNED NOT NULL PRIMARY KEY,
    ...
    CONSTRAINT FOREIGN KEY (id) REFERENCES object_ids (id)
) ENGINE=InnoDB;

/* When creating a new record, first insert your object type into the object_ids table */
INSERT INTO object_ids(object_type) VALUES ('event');
/* Then, get the auto-increment id. */
SET @id = LAST_INSERT_ID();
/* And finally, create your object record. */
INSERT INTO events (id, ...) VALUES (@id, ...);

Obviously, you would duplicate the structure of the events table for your other tables.

Answer 2

UUID_SHORT() should do the trick. It will generate 64-bit unsigned integers for you.

According to the doc the generator logic is:

(server_id & 255) << 56
+ (server_startup_time_in_seconds << 24)
+ incremented_variable++;

The value of UUID_SHORT() is guaranteed to be unique if the following conditions hold:

• The server_id value of the current server is between 0 and 255 and is unique among your set of master and slave servers

• You do not set back the system time for your server host between mysqld restarts

• You invoke UUID_SHORT() on average fewer than 16 million times per second between mysqld restarts

mysql> SELECT UUID_SHORT();
    -> 92395783831158784

If you curious what is your server id you can use either of these:

SELECT @@server_id
SHOW VARIABLES LIKE 'server_id';