3

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.

Improve this question
2
  • You may want to work with a form of datenum: stackoverflow.com/questions/6030071/…; and drop the time of day from the datenum for record increment instead. Commented Jul 3, 2015 at 16:24
  • possible? sure! good? sure not! Just use 2 columns: (auto) Identity and Date. If you create exotic beast they ill come back and bit you Commented Jul 3, 2015 at 17:12

2 Answers 2

Reset to default
2

"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)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.