I have this table
Primary key is (Name, FromDate). ToDate can be NULL. I would like to ensure that there are no rows with overlapping values. For example
Is there a way to stop SQL from allowing this to be inserted into the table? I know that it can be done in a trigger or a stored procedure, but was wondering if there was any way to do it using a Rule or some other method?
Any ideas welcome.
You could use a check constraint to do this, but I wouldn't recommend it. Triggers or logic in the data layer (stored procedures or sql code) would be better.
I'm away from an instance at present so can't knock one together, but I'll post later.
Edit : time to add some example code
This code creates a function that returns 1 if there is more than 1 row that meets the criteria - you may need to adjust for the null value of
Like I said earlier, this isn't the best way, and will perform horrendously, but it fits your (strange) requirement.
I'm presuming that the date range only needs to be checked within the same
There is no posibility except triggers or stored procedures as you have mentioned to handle this.
Anywa It's costly operation especially on bigger tables and you can lower the cost by by creating index on the date columns.
I know this is an oldish thread, but I thought this could only be done by triggers or sprocs until I recently read a Jo Celco article on how to do this with only referential integrity. Unfortunately I can't find the article any more, but IIRC, it went something like this like this:
Quite a lot of bits to add, and it does make it hard to insert records, so you still really need a trigger, but a good way to ensure that even data added manually by developers with those pesky triggers turned off is definitely valid.
answered Oct 17, 2011 at 03:43 AM