I am creating a time series database that follows the table structure here: Storing intervals of time with no overlaps. The difference with my design is that the FinishedAt column changes every time a user updates that particular record (you can actually see my question on his site at the bottom).

Basically, I have a FinishedAt column that has the current record with a date of 9999-12-31 until the next record with the same business key gets updated (in his example that key would be the SettingID). I run into the problem with his FK_IntegerSettings_SettingID_PreviousFinishedAt since I cannot make FinishedAt part of my primary key. I cannot make it part of my primary key because it is always changing....but is it okay to make FinishedAt part of my primary key, which would involve updating the primary key? Is this frowned upon even in a time series table?

I realize I can still use his other constraints, but I loose the very important foreign key constraint. This is my first encounter with this type of design so any direction will be appreciated. Thank you.

  • Unfortunately your link is hidden behind a log-in wall :( – Michael Kargl Mar 25 at 12:00

A foreign key can reference a unique constraint, not necessarily only a primary key, so if you want to be a purist, include FinishedAt (and whatever else you need) in the parent table's unique constraint and use that to define the foreign key.

| improve this answer | |
  • I did not know you could do that (you learn something new every day, Thanks!) @mustaccio – jmzagorski Jun 26 '15 at 20:18
  • 1
    Just want to add that a foreign key can reference a unique index too, not just a primary key or unique constraint. Personally, I prefer unique constraints rather than unique indexes when the purpose is data integrity rather than just performance. – Dan Guzman Jun 27 '15 at 11:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.