How to maintain unique sequence across table columns?

Question

I hava two tables registed_products and unregisted_products. Both have autoincrement primary key which can be overlapping which if 100 id exist reg table, it can exist in unreg table as well. Insert can come from many places with in the application

There is third service expecting unique id for both type of products.

In oracle , I could have introduced one more column in each table. Create a sequence. Now could create the trigger before_insert on both the tables so that whenever there is an insert in any table, trigerr will get the next_val from sequence and insert in to new column. This way I can ensure unique number across table columns.

I am not sure how to achieve the same in mysql as there are triggers but not sequences that can be used across tables ?

• dbdemon: Probably unhelpful, but if MariaDB is an option instead of MySQL, then that has sequences since version 10.3.

Answer 1

Plan A: Have a 3rd table that contains an AUTO_INCREMENT plus any common columns between the two existing tables.

Plan B: Build a "sequence" table which has 2 columns: an AUTO_INCREMENT and another column to key into the table. Probably REPLACE is the command to get a new id without growing the table.

REPLACE is performed in 2 steps:

1. DELETE any row(s) that have match on any UNIQUE key.
2. INSERT a new row.

Here's the table and its initialization:

 CREATE TABLE sequence (
 seq INT UNSIGNED AUTO_INCREMENT NOT NULL,
 code CHAR(1) CHARACTER SET ascii NOT NULL,
 PRIMARY KEY(seq),
 UNIQUE(code) ) ENGINE = InnoDB;
 INSERT INTO sequence (seq, code) VALUES (1, 'a');

To get the new 'next' sequence number, either use REPLACE:

 REPLACE INTO sequence (code) VALUES ('a');
 SELECT seq FROM sequence WHERE code = 'a';

Or, and this is probably faster,

 INSERT INTO sequence (seq, code)
 VALUES (NULL, 'a')
 ON DUPLICATE KEY UPDATE
 seq = LAST_INSERT_ID(seq + 1);
 SELECT LAST_INSERT_ID();

• user3198603: Not sure what do you mean by Probably REPLACE is the command to get a new id without growing the table. ?
• Rick James: @user3198603 - I have added more details.

Answer 2

Create a view on each of the tables, and on one, replace Id with Id * 2 so it will always be an even number. In the other, replace Id with Id * 2 + 1, so it will always be odd.

The numbers will be unique across the two tables, and it is always possible to go from original Id to the view's one and then back to the original.

Or just add the calculated columns to the views calling the new column e.g. UniqueId and include the original Id in the view. That way all information is preserved without having to do any more calculation.

Answer 3

Simon's answer is a good one. He effectively suggests splitting the range of possible ids into 2. This can also be done in a more flexible manner by using a bitshift right operator. You can then decide how many ranges you want in the same 64(?) bit number space to have by deciding on how many shifts right you move the number. I don't know about MySQL but a simple check constraint (like in PostgreSQL) can enforce this on the id columns of each table and is very fast.

We've done this in our reference database and it allows us to use a single 64 bit id field across all tables in the database with a guarantee of no re-use between tables.