7

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

unamashana
  • 635
  • 1
  • 5
  • 14

2 Answers2

1

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

Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
  • 1
    Isn't this is a common scenario? For example in a SaaS solution, a company could have many cases and you may want a case_id which starts from 1 (and keeps incrementing) for every company. How do you implement something like this at database level? – unamashana Feb 09 '11 at 11:33
0

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.

Vasil Popov
  • 1,210
  • 14
  • 22
  • Can surely do it this way but I was wondering if there was a way to do this at the database level. If you see its an autoincrement scoped on a foreign key – unamashana Feb 09 '11 at 11:34
  • Yes, sure. But the funcitonality you require is not present in MySQL. – Vasil Popov Feb 09 '11 at 14:49