Home » SQL & PL/SQL » SQL & PL/SQL » Check Constraints with Date Ranges !!! URGENT (Oracle Express)
icon8.gif  Check Constraints with Date Ranges !!! URGENT [message #436887] Tue, 29 December 2009 06:34 Go to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1831
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
@ i.harry

i.harry wrote on Tue, 29 December 2009 19:02
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.!!!!



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 Smile
Re: Check Constraints with Date Ranges !!! URGENT [message #436903 is a reply to message #436900] Tue, 29 December 2009 07:47 Go to previous messageGo to next message
joy_division
Messages: 4616
Registered: February 2005
Location: East Coast USA
Senior Member
i.harry wrote on Tue, 29 December 2009 08:32
Oh 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 #436904 is a reply to message #436900] Tue, 29 December 2009 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read OraFAQ Forum Guide and follow it or stay away.
We don't care of disrespectful people that break rules of the forum.

WE are providing help for free and spend our time for this, so WE are the ones that knows what is urgent or not in this forum and "you are in no position to tell US what's urgent or not".

Quote:
Or is it because you like being the big man on forums like this where less experienced people require assistance.

Or is it because you like being the big man on forums like this where less experienced people like you DEMAND assistance.
If you want a urgent help, call Oracle or a consultant and pay for it.

Quote:
Stop being a dick and try to be polite, cause your not bigger or better than me or anyone else.!!!!

This very well applies to you.

Regards
Michel

Re: Check Constraints with Date Ranges !!! URGENT [message #436905 is a reply to message #436900] Tue, 29 December 2009 07:55 Go to previous message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
i.harry wrote on Tue, 29 December 2009 14:32
Oh 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.
Previous Topic: Store big data into variable
Next Topic: sql problem
Goto Forum:
  


Current Time: Tue Sep 27 21:11:47 CDT 2016

Total time taken to generate the page: 0.13580 seconds