How to have unique identifiers in two or more tables in MySQL?

Question

I have a table called events where all the new information goes. This table acts as a link for all queries for news feeds, so the elements of the event are selected from there, and the information corresponding to this event is extracted from the correct tables.

Now, here is my problem. I have an E_ID in the event table that matches the event ID in another table, be it T_ID for tracks , S_ID for status , etc. These identifiers may be the same, so for now I just used a different auto_increment value for each table, so status started at 500 tracks at 0, etc. Obviously, I don’t want to do this, because I still don’t know from which table getting more data from it. I would suggest that status will quickly exceed tracks .

Information is inserted into the event table with triggers. Here is an example of one;

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

This is for the status table.

Is there any addition to this trigger that I can do to provide NEW.S_ID ! = An E_ID currently in events , and if it really changes S_ID .

Alternatively, there is some kind of key that I can use to refer to events when S_ID automatically incremented S_ID that S_ID does not increase to E_ID .

These are my thoughts, I think the last solution would be better, but I doubt that it is possible or it is, but it will require another look-up table and will be too complicated.

Answer 1

It is really unusual to require a unique identifier in tables, but here is the solution that will make 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 are duplicating the structure of the events table for other tables.

Answer 2

You can also use a universal unique identifier (UUID).

UUID is designed as a number globally unique in space and time. It is expected that two calls to UUID () will generate two different values, even if these calls are made on two separate computers that are not connected to each other.

Read more about this in the manual .

There is also a shorter version .

Answer 3

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

According to the document, the logic of the generator:

 (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 are true:

• The server_id value of the current server is in the range from 0 to 255 and is unique among your set of primary and subordinate servers.

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

• You call UUID_SHORT () on average less than 16 million times per second between mysqld reboots

 mysql> SELECT UUID_SHORT(); -> 92395783831158784 

If you are interested in what server identifier you have, you can use any of them:

 SELECT @@server_id SHOW VARIABLES LIKE 'server_id';