MySQL/Percona: Date Range data integrity (via natural key)

Question

I'm creating a schedule table, given the user the ability to schedule certain items on their site. The table looks like this, essentially:

schedules
---------
item_id
start_date
end_date
...

I'm trying to create a natural key (UNIQUE key) that makes some sense. Technically, the natural key should be something like [item_id, date range], where date ranges can't overlap. But obviously, I need something workable in the real world :)

Is there anything I can do to set up a unique/natural key that makes sense?

Thanks!

Answer 1

Assuming you mean that the date range can't overlap for a given id then, no, I don't think there is any way to do this with only a unique key constraint. But you could require id to be non-null and add a before insert/update trigger which sets id to null (forcing an error) if the resulting data would overlap.