Menu
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
351
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
MySQL has the AUTO_INCREMENT keyword to perform auto-increment. The starting value for AUTO_INCREMENT is 1, which is the default. It will get increment by 1 for each new record. To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT.
MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. VALUES ('Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table.
Auto increment is used with the INT data type. The INT data type supports both signed and unsigned values. Unsigned data types can only contain positive numbers. As a best practice, it is recommended to define the unsigned constraint on the auto increment primary key.
No, no such thing exists. Either allow the auto_increment to be unique across the table, or you have to implement it yourself.
121
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
