Check Constraints with Date Ranges !!! URGENT [message #436887] |
Tue, 29 December 2009 06:34  |
i.harry
Messages: 2 Registered: December 2009
|
Junior Member |
|
|
Hi. I'm a total novice at SQL and Oracle. I have a question which I do not really know how to approach. As an example of the issue I have the following table layout:
USER_ID : STARTDATE : ENDDATE: :
BobI : 29-12-2009 : 23-01-2010 :
This row shows BobI is NOT available between the above dates. So I would like to add a check constraint stating that if a new row is added which falls in that date range a duplicate of BobI can not exist. for example
USER_ID : STARTDATE : ENDDATE :
BobI : 01-01-2010 : 10-01-2010 :
Can NOT exist (Falls in the above date range), but
USER_ID : STARTDATE : ENDDATE: :
BobI : 24-01-2010 : 26-01-2010 :
Can (Outside of date range)!!
The logic in my head is something like:
USER_ID = UNIQUE where STARTDATE>=STARTDATE and ENDDATE<=ENDDATE.
The table already exists so this would be an amend which addition of check. I am open to suggestions so if there is an easier way please do let me know. Thanks.
|
|
|
Re: Check Constraints with Date Ranges !!! URGENT [message #436897 is a reply to message #436887] |
Tue, 29 December 2009 07:20   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Firstly: This is not URGENT (and yes, yelling it does not emphasize this).
Secondly: The only thing you may ensure with check constraint is, that STARTDATE <= ENDDATE in one row.
Interval overlapping check among multiple row is possibble using trigger. Please, urgently study it in the SQL Reference book, available with other Oracle documentation e.g. online on http://tahiti.oracle.com/
Quote:The logic in my head is something like:
USER_ID = UNIQUE where STARTDATE>=STARTDATE and ENDDATE<=ENDDATE
Does not seem to be correct. I would compare STARTDATEs with ENDDATEs instead. I will let deriving the exact conditions on you (as this has nothing to do with Oracle and you may easily check it with given examples).
|
|
|
Re: Check Constraints with Date Ranges !!! URGENT [message #436900 is a reply to message #436897] |
Tue, 29 December 2009 07:32   |
i.harry
Messages: 2 Registered: December 2009
|
Junior Member |
|
|
Oh Dear.. Firstly you are in no position to tell me what's urgent or not. But thanks anyway for the prompt response. Now back to your attitude, is it because you are not happy in life or you didn't get laid yesterday? Or is it because you like being the big man on forums like this where less experienced people require assistance. If you do not wish to help, why waste time here. Are you one of those geeks who like to show how superior they are just cause they have a small penis? Is this why you use a name like flyboy? Stop being a dick and try to be polite, cause your not bigger or better than me or anyone else.!!!!
|
|
|
Re: Check Constraints with Date Ranges !!! URGENT [message #436901 is a reply to message #436900] |
Tue, 29 December 2009 07:40   |
 |
ramoradba
Messages: 2457 Registered: January 2009 Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
@ i.harry
i.harry wrote on Tue, 29 December 2009 19:02Oh Dear.. Firstly you are in no position to tell me what's urgent or not. But thanks anyway for the prompt response. Now back to your attitude, is it because you are not happy in life or you didn't get laid yesterday? Or is it because you like being the big man on forums like this where less experienced people require assistance. If you do not wish to help, why waste time here. Are you one of those geeks who like to show how superior they are just cause they have a small penis? Is this why you use a name like flyboy? Stop being a dick and try to be polite, cause your not bigger or better than me or anyone else.!!!!
Quote:By clicking the Agree button, you warrant that you will not post any messages that are obscene, vulgar, sexually-oriented, hateful, threatening, or otherwise violate any laws.
The owners of this forum have the right to remove, edit, move or close any topic for any reason.
Please read this
especially
Quote:
Be polite
and DO NOT use IM-speak!
Avoid the following keywords: urgent/please/help/now/immediately (and derivatives)
sriram
|
|
|
Re: Check Constraints with Date Ranges !!! URGENT [message #436903 is a reply to message #436900] |
Tue, 29 December 2009 07:47   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
i.harry wrote on Tue, 29 December 2009 08:32Oh Dear.. Firstly you are in no position to tell me what's urgent or not.
Incorrect. If it's so damn urgent then go and pay someone to solve your problems otherwise shut your mouth. You are not paying us therefore we owe you nothing, either in a timely manner or even a correct answer.
|
|
|
|
Re: Check Constraints with Date Ranges !!! URGENT [message #436905 is a reply to message #436900] |
Tue, 29 December 2009 07:55  |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
i.harry wrote on Tue, 29 December 2009 14:32Oh Dear.. Firstly you are in no position to tell me what's urgent or not. But thanks anyway for the prompt response. ... Stop being a dick and try to be polite, cause your not bigger or better than me or anyone else.!!!!
Thank you for showing me the "real" politeness.
Anyway, I gave you some points you may follow in your investigation; maybe you just overlooked them. If you are expecting to be spoonfeeded, well, not from me.
|
|
|