CODEWITHSUNDEEP
mysqlruby-on-railsauto-increment
unamashana
unamashana

Reputation: 635

Scoped AUTO_INCREMENT in MySQL?

I am using MySQL in a rails application. I have a users table (standard stuff like id, name etc) and a books table (again with id, user_id and title etc).

I would like to have a column (lets call it user_book_id) that should be auto incremented like id but scoped with user_id. Just like id, even if a record is deleted in the books table, the user_book_id should not be reused. An example:

User

id | Name
------------
1  | Jerry
2  | Newman

Book

id | user_id | user_book_id | Title
-----------------------------------
1  | 1       | 1            | Jerry's First Book
2  | 1       | 2            | Jerry's Second Book
3  | 2       | 1            | Newman's First Book
4  | 1       | 3            | Jerry's Third Book

Is there a way to do this in MySQL? I searched but could not find anything.

Thanks, Prateek

Upvotes: 7

Views: 1080

Answers (2)

Vasil Popov
Vasil Popov

Reputation: 1248

You can implement it by yourself, lets say if you put the last user_book_id value in a new column in the users table, then when inserting in books, take the value from the new column in users and increment +1.

Upvotes: 0

Dan Grossman
Dan Grossman

Reputation: 52372

No, no such thing exists. Either allow the auto_increment to be unique across the table, or you have to implement it yourself.

Upvotes: 1

Related Questions