Copied AUTO_INCREMENT in MySQL?

Question

I am using MySQL in a rails application. I have a user table (standard elements such as id, name, etc.) and a book table (again with identifier, user_id and name, etc.).

I would like to have a column (let's call it user_book_id) that should be automatically incremented as id, but in a field with user_id. Like id, even if the entry is deleted in the workbook table, user_book_id should not be reused. Example:

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

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

Thanks Prateek

Answer 1

No, this does not exist. Either allow auto_increment to be unique throughout the table, or you need to implement it yourself.

Answer 2

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