CODEWITHSUNDEEP
mysqlsqlpostgresql
cbrad
cbrad

Reputation: 185

String primary key comprised of date and auto incrementing integer

Basically for every record I want primary key of date + incrementing integer, and the integer resets every day.

Is this possible in SQL?

Edit: Thanks for the responses. I would consider doing this at the application layer, but the painful part is accessing the integer part. Another solution is to calculate the date at the application layer, update/retrieve the integer then insert the new record. Then reset the the variable at the end of the day.

Upvotes: 3

Views: 144

Answers (2)

David Aldridge
David Aldridge

Reputation: 52386

If you want the numbers to increase monotonically then you have to implement a locking mechanism to prevent concurrent inserts. If you only have a single session inserting data then that's fine, but it's difficult with multiple sessions.

This is a very bad design pattern for concurrency, and you'd do better with a regular sequence-generated PK, a separate date column, and another column that you populate asynchronously with the number series.

Upvotes: 1

Bonatti
Bonatti

Reputation: 2781

"Is this possible in SQL?"

Yes

I would suggest having 2 rows with each value (one for the Date, one for the Integer).

What you would need is the following: a trigger for insertions that adds the NOW() value to a field. Then concat the Integer to that Date.

And another trigger to reset the Integer value at the change of day (the hour you chose)

Upvotes: 2

Related Questions