1

I have 2 tables 'table1' & 'table2', both have column named 'id' (primary key) and both have auto increment. The problem is how do I make the primary key viable for the both tables.

Example: I enter id '100' in 'table1' and after that if I try to enter a new record in 'table2' I would have '101' in 'table2'

I thought 'foreign key' would do the job but it did the exactly opposite of what I need

dev
  • 735
  • 2
  • 15
  • 31
  • 6
    Why do you need this? IDs for each table should be independent. If they're not, there's something wrong with your schema, they should actually be the same table. – Barmar Aug 18 '14 at 19:08
  • can you post your database schema? – letsjak Aug 18 '14 at 19:12
  • You don't need that. There doesn't exist a scenario where anyone would need such functionality. You might think you need it, but you don't. That's the cold truth, without even taking a look at your requirement or data model. Now, the cool part about StackOverflow is that you can present your problem and ask about possible solutions. Not for something you *think* is a solution. Classic XY problem. – N.B. Aug 18 '14 at 19:38

1 Answers1

1

Strictly speaking, there's no way to do what you describe without resorting to extra queries or extra locking.

You can simulate it by creating a third table, into which you insert to generate new id's, then use LAST_INSERT_ID() to insert into either table1 or table2. See example in How to have Unique IDs across two or more tables in MySQL?

Also see some discussion of this problem and solutions here at the popular MySQL Performance Blog: Sharing an auto_increment value across multiple MySQL tables

But I agree with the comment from @Barmar, this might be an indication of a bad design for a database. If you have a requirement to make the auto-increment PK unique across multiple tables, this may mean that the two tables should really be one table.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828