Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I'm having to design a table which contains scheduled times for media to be displayed. These scheduled times should not overlap.
In my SheduledSlot table I have:
id integer PRIMARY KEY
begin datetime(or integer for SQLite)
end datetime(or integer for SQLite unixtime)
media_item integer (foreign key)
How do I enforce a constraint which ensures a unique time range for each scheduled item?
You should implement this on the database side in the method that can be called by your create schedule time and modify schedule time methods.
You can implement such a functionality using triggers. It can be INSTEAD INSERT/UPDATE or BEFORE INSERT/UPDATE trigger depends on RDMS you are using. In the trigger body you raise an error if the new data does not pass validation.
