0

I have different tables that need to share a pool of primary keys.

E.g. table A has primary keys 0 to 9, table B has primary keys 10 to 19.

Is there a way to tell mysql where to draw the primary key from? Like a stored procedure? It would be enough to execute a script that knows about the distribution of the buckets.

A naive approach would be to set the primary key within each insert statement. That PK is drawn from that pool. Actually, this would need to be implemented within each application (like php or java) that inserts into that DB which I want to avoid.

Aman Aggarwal
  • 17,619
  • 9
  • 53
  • 81
user3169506
  • 93
  • 2
  • 4

1 Answers1

1

I don't understand why tables need to share primary keys.

Your description already betrays a problem. Primary keys should be meaningless (or, at most, can indicate insertion order). Specific ranges should not be allocated for different purposes.

Instead, each table can have its own auto incremented id. One possibility is to have another table with an auto-incremented id, and use a foreign key reference to that table. The downside is that you need to insert into both tables for each insert -- something a trigger can do quite easily.

Instead of "ranges" of primary keys, you should have an explicit type.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786