4

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?

Edward Williams
  • 141
  • 3

2 Answers2

3

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.

Leigh Riffel
  • 23,854
  • 16
  • 78
  • 152
2

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.

a1ex07
  • 9,000
  • 3
  • 24
  • 40