CODEWITHSUNDEEP
mysqlauto-increment
Daniel
Daniel

Reputation: 108

AUTO_INCREMENT for two tables

I have two tables in my database for users:

users

id|username|password|registration_date|
1 |bruce   |******  |2017-03-04       |
2 |jason   |*****   |2017-03-06       |
3 |brad    |******* |2017-03-12       |

google_users

id|username|password|registration_date|
1 |jimmy   |*****   |2017-03-05       |
2 |wade    |******* |2017-03-08       |

I want to apply the same AUTO_INCREMENT index for both tables when a new user signs up with google.

Something like this:

users

id|username|password|registration_date|
1 |bruce   |******  |2017-03-04       |
3 |jason   |*****   |2017-03-06       |
5 |brad    |******* |2017-03-12       |

google_users

id|username|password|registration_date|
2 |jimmy   |*****   |2017-03-05       |
4 |wade    |******* |2017-03-08       |

How can I do this?

Upvotes: 2

Views: 2283

Answers (2)

Krishnakumar
Krishnakumar

Reputation: 745

Not suggested, but if you really want it to happen this way:

You can try to implement this setting in MySQL:

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 2     |

and then for your tables, you can do: 

ALTER TABLE users AUTO_INCREMENT = 1;
ALTER TABLE google_users AUTO_INCREMENT = 2;

So, now, your auto-increment will be incremented by 2 and it gives you the expected result.

But as I said, this will impact your whole DB. All your increments will be done by 2 instead of 1.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

I'm going to vote against this table design and recommend that that you just maintain a single users table:

users (id, username, password, registration_date)

To keep track of the method by which they signed up, you may create a second table:

accounts (id, user_id, type_id)

The type_id can point to yet a third table, indicating whether Google or something else were the source of the signup. Note also that the accounts table can have a user with more than one signup relationship, if you would need that.

The basic idea is that maintaining an auto increment column across two tables will either be impossible, or at the very least ugly. This is not a feature which is usually supported/needed in SQL. So if you find yourself having this need, you should first look closely at your database design.

Upvotes: 1

Related Questions