Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jafrywilson
Constraint Violating Yak Guru
379 Posts |
Posted - 2012-05-22 : 03:37:55
|
my table is like thisid start_date end_date1 2012-05-01 08:30:00.000 2012-05-30 09:30:00.000if i am adding another row like start_date 2012-05-01 09:00:00.000it should not be inserted ..how to check this condition ?thanks for your reply |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-05-22 : 05:28:52
|
quote: it could not be inserted ..
What is the error message ? KH[spoiler]Time is always against us[/spoiler] |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2012-05-22 : 06:46:10
|
So the same start_date should not be added again?MadhivananFailing to plan is Planning to fail |
|
|
jafrywilson
Constraint Violating Yak Guru
379 Posts |
Posted - 2012-05-22 : 08:46:20
|
Yes.. Start_date should not be added again if the start date is same even in between the start and end_time...It is an appointment table.. When im having one appointment I should not insert another appointment |
|
|
jafrywilson
Constraint Violating Yak Guru
379 Posts |
Posted - 2012-05-22 : 08:47:16
|
quote: Originally posted by khtan
quote: it could not be inserted ..
What is the error message ? KH[spoiler]Time is always against us[/spoiler]
sorry for my poor english |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-05-22 : 10:53:04
|
I know you can apply that logic with a trigger. You might also be able to use a check constraint with a function. |
|
|
jafrywilson
Constraint Violating Yak Guru
379 Posts |
Posted - 2012-05-26 : 07:50:16
|
Thanks for your reply.. How to write query to check this condition... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-26 : 22:10:33
|
quote: Originally posted by jafrywilson Thanks for your reply.. How to write query to check this condition...
you need to use check constraint with a udf like belowhttp://visakhm.blogspot.com/2012/05/implementing-multiple-table-based-check.htmlin your case function will beCREATE FUNCTION CheckDateOverlap(@Date datetime)RETURNS bit ASBEGINDECLARE @Ret bitSELECT @Ret = CASE WHEN COALESCE(Cnt,0) > 0 THEN 0 ELSE 1 ENDFROM (SELECT COUNT(*) AS Cnt FROM tablename WHERE start_date < @date AND end_date > @Date )tRETURN (@ret)ENDthen use it in check constraint likeALTER TABLE tablename ADD CONSTRAINT Chk_DateOverlap CHECK (dbo.CheckDateOverlap(start_date) = 1 ) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|