Clever Geek Handbook

String primary key consisting of a date and an autointeger

Basically for each record, I want to have a primary key of the date + integer increment, and the integer is reset every day.

Is this possible in SQL?

Edit: Thanks for the answers. I would think about it at the application level, but the sore part is referring to the whole part. Another solution is to compute date

at the application level, update / retrieve an integer and then insert a new record. Then reset the variable at the end of the day.

+3


source to share


2 answers


"Is this possible in SQL?"

Yes

I would suggest having 2 lines with each value (one for date, one for Integer).



You will need the following: an insert trigger that adds a NOW () value to the field. Then split the Integer by that date.

And one more trigger to reset the Integer value when the day changes (the hour you selected)

+2


source


If you want numbers to increase monotonically, you need to implement a locking mechanism to prevent concurrent inserts. If you only have one session inserting data then this is fine, but it is difficult with multiple sessions.



This is a very bad design pattern for concurrency, and you will be better off with regular generation of PK sequence, separate date column and another column that you fill asynchronously with a series of numbers.

+1


source






More articles:


All Articles