10

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.

Steven Moseley
  • 15,871
  • 4
  • 39
  • 50
Jacob Windsor
  • 6,750
  • 6
  • 33
  • 49
  • 4
    I would suggest that if you really need these you allocate the id as an auto increment field on one table, and use the value from that as the new id when populating a field on your various other tables. However I would prefer to have the id fields on the various table totally independent. – Kickstart Jun 06 '13 at 12:10

3 Answers3

15

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.

Steven Moseley
  • 15,871
  • 4
  • 39
  • 50
  • I know it's old, and while inserting is covered, but deletion (and changing) seems a bit harder. As well as that it required discipline from the application in all cases. It feels like a solution with triggers could be an avenue, just beyond me right now. –  Mar 03 '18 at 20:35
  • Triggers could work for deletion, if required. However, I can't think of a DB solution in which permanent deletion should be required. An "is_active" status flag on objects should be suitable for 99% of cases. – Steven Moseley Mar 04 '18 at 22:09
3

You could also just use a Universal Unique Identifier (UUID).

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

Please read more about it in the manual.

There's also a shorter version.

fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • 7
    The downside of using UUIDs is that as your table grows in size, insertion becomes extremely slow. Because your ID is your primary key, it's your clustered index. Every time you insert a UUID, the entire table structure will have to be resorted. This can take MINUTES when you get a table with millions of records. An auto-incrementing value will always go at the end, so no resorting is necessary. – Steven Moseley Dec 03 '13 at 11:12
  • 1
    @StevenMoseley What about using a bigint as your primary key, and using a UUID as a secondary column? You wouldn't be enforcing integrity, you'd only be assuming it, but depending on your needs that could be enough. Worst case you can manually check for duplicate UUID when you need that level of certainty i.e. if the data is being inserted from external API call and you are unsure of where/how the UUID was generated. – Le-roy Staines May 11 '16 at 21:32
  • @Le-roy just seeing this question. The downsides to that are: 1) you would be creating a "throw-away" clustered index. In MySQL, the primary (clustered) index is BY FAR the fastest for seek time, because the data is actually sorted in that order, whereas all other index types contain pointers to the data... 2) the UUID index itself will be sorted on insert, which will make inserts slower, although not as much as PK UUID), and... 3) finally, a UUID has 9x the size of an INT. MySQL indexes already take up a lot of disk space. Why complicate them by making them 9x bigger than they need to be? – Steven Moseley Aug 10 '16 at 14:55
  • 1
    @StevenMoseley There are pros and cons to UUID's but by using it as a secondary column and enforcing the uniqueness yourself, when required, you can easily achieve inserts sub-millisecond on tables larger than a billion rows and still have the benefits of using a UUID and not necessarily trusting a sequential UUID either. – Le-roy Staines Aug 10 '16 at 22:29
  • @Le-roy definitely. In fact, I use UUIDs throughout my own application designs, but never as a `PRIMARY` or `UNIQUE` key in MySQL The only good uses of a UUID, in my opinion, are 1) for obfuscation of PK (e.g., for publicly accessible data by id, to prevent guessing), or 2) to actually create a Universally Unique Identifier as intended by its design, i.e. for the prevention of identity collision in a distributed system that spans multiple servers. – Steven Moseley Aug 11 '16 at 15:19
  • 1
    @StevenMoseley - we use them for letting users create relational data in offline systems that can be partially or fully synchronized later, but we keep a bigint as the primary identity for internal performance. That lets the external users create identities without having to be in an online state. I don't know if that's the accepted way of doing it, but that's why we use em :) – Le-roy Staines Aug 12 '16 at 00:54
  • @Le-roy sounds like the intended design to me! "Identifier" doesn't necessarily imply "PK", right? – Steven Moseley Aug 15 '16 at 18:17
  • 1
    @Le-royStaines so, a few years later, and I'm a full convert, using UUID pseudo-PKs everywhere now..https://stackoverflow.com/questions/3936182/using-a-uuid-as-a-primary-key-in-django-models-generic-relations-impact/58737923#58737923 – Steven Moseley Mar 25 '21 at 23:48
  • 1
    @StevenMoseley How you managed to keep a memory of this conversation is what impresses me!! Here's how I'm doing it.. https://stackoverflow.com/a/37202747/2247427 – Le-roy Staines Mar 28 '21 at 04:26
  • @Le-royStaines hah! I didn't... I got an upvote on my answer yesterday and while reading the comments found this thread, and thought I'd let you know I'm a convert! Checking out your method now :D – Steven Moseley Mar 29 '21 at 01:54
  • @StevenMoseley That's awesome :D – Le-roy Staines Mar 29 '21 at 11:45
1

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';
Pavel
  • 3,967
  • 2
  • 29
  • 35