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.
(comments are locked)
|
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
May 19, 2011 at 09:30 AM
Pavel Pawlowski
Yes, that will perform horrendously, but I suspect (without actual testing) that the performance would be as good or better than using a trigger. There are definitely advantages to doing this with either a check constraint or a trigger instead of passing it to the application layer or requiring the use of stored procedures since it is possible more than one application will work with this data and someone with permissions could always access it through SSMS. The constraints or trigger is much harder to bypass if this is a major integrity requirement.
May 19, 2011 at 10:38 AM
TimothyAWiseman
Good example @Kev.. Only one thing:
I rather use `EXISTS` instead of
Instead of
As the
May 19, 2011 at 10:51 AM
Pavel Pawlowski
@Pavel, I couldn't get the right results with
May 19, 2011 at 11:13 AM
Kev Riley ♦♦
@Kev that's pitty that the check is fired after the insert.. Then of cousre the
May 19, 2011 at 11:47 AM
Pavel Pawlowski
(comments are locked)
|
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. Thanks for all your input. Kev's method works perfectly. Performance is not a big issue for my implementation because there will only ever be a handful of inserts/updates.
May 20, 2011 at 01:46 AM
xnl28
(comments are locked)
|
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.
(comments are locked)
|