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.

cbrad
  • 163
  • 1
  • 15
  • You may want to work with a form of datenum: http://stackoverflow.com/questions/6030071/mysql-table-insert-if-not-exist-otherwise-update; and drop the time of day from the datenum for record increment instead. – jdl Jul 03 '15 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 – jean Jul 03 '15 at 17:12

2 Answers2

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)

Bonatti
  • 2,778
  • 5
  • 23
  • 42
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.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96