1

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.

1
  • Unfortunately your link is hidden behind a log-in wall :( Mar 25, 2020 at 12:00

1 Answer 1

4

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.

2
  • I did not know you could do that (you learn something new every day, Thanks!) @mustaccio
    – jmzagorski
    Jun 26, 2015 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, 2015 at 11:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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