How to achieve unique auto-incremented id for rows across multiple tables?

Question

I've got several tables in a database, let's say they're called table1, table 2, etc. All tables have a primary key column 'id' with auto-increment.

In my current configuration it happens that when inserting into table1, the generated id is 1. Afterwards when inserting into table2, the generated id happens to be 1 as well.

How to force absolutely unique ids across all tables in a database? I want when inserting into table1, generated id to be 1, and if afterwards inserting into table2, generated id be 2?

I used mysql server on some machine and did not have this problem, but when I installed mysql on my local machine, it started to occur. So I guess it must be some kind of a setting that is applied to the mysql configuration?

Thank you

You'll need an id table. Instead of autgenerating the id in each individual table, grab the next id from the id table, then increment the value in the id table. — Mike Gardner, May 02 '13 at 11:30
@legendinmaking because of limitations in the application layer — Martin Asenov, May 02 '13 at 11:30
what if you check the `last_Inserted_Id` from table1 and when in table2 sum `table1.id+1`? It could work fine. — A. Cristian Nogueira, May 02 '13 at 11:34
Alternatively, depending on how big you expect your tables to get, you can set the AUTO_INCREMENT = VALUE for each table. EG. Table A starts at 10000000 Table B starts at 20000000. As long as you have a large enough gap between the start values to support the expected size of the table. — Mike Gardner, May 02 '13 at 11:43
@MichaelGardner your first suggestion is good. However if I set offsets to ids as you suggest in your second comment is not a good practice. You get yourself limited and it may become hard to scale. — Martin Asenov, May 02 '13 at 14:17
@MartinAsenov - I agree, it's not the best practice, but it requires minimal code changes, and is quick to implement. — Mike Gardner, May 02 '13 at 14:20

score 3 · Accepted Answer · answered May 02 '13 at 12:19

3

you can use UUID.

INSERT INTO mytable(id, name) VALUES(SELECT UUID(), 'some data');

Read more about UUID: http://mysqlbackupnet.codeplex.com/wikipage?title=Using%20MySQL%20With%20GUID%20or%20UUID

answered May 02 '13 at 12:19

mjb

7,649
8
44
60

is there a way to achieve this with bigint type ids? – Martin Asenov May 02 '13 at 13:08
@MartinAsenov If you want to use bigint, you can try Michael Gardner suggestion. Use a separate id table. – mjb May 02 '13 at 13:25

Smile · Answer 2 · 2013-05-02T13:15:44.393

0

You can create SEQUENCE which can be used globally.

  CREATE SEQUENCE serial
  INCREMENT 1
  MINVALUE 0
  MAXVALUE 200
  START 364
  CACHE 1;

Edit: Sequences are supported in Postgres but this can be achieved in MySql by setting value for AUTO_INCREMENT and one can use LAST_INSERT_ID(). link

edited May 02 '13 at 13:15

answered May 02 '13 at 11:40

Smile

2,770
4
35
57

There are no sequences in MySQL – Martin Asenov May 02 '13 at 13:03
@MartinAsenov: Good to pointing out. I written like I was writing for Postgres. – Smile May 02 '13 at 13:16

How to achieve unique auto-incremented id for rows across multiple tables?

2 Answers2

Linked

Related