Home » SQL & PL/SQL » SQL & PL/SQL » Enforcing business rule (Oracle 9i or Oracle10g)
Enforcing business rule [message #357442] Wed, 05 November 2008 06:07 Go to next message
hrishy
Messages: 25
Registered: August 2005
Junior Member
Hi

I need to enforce this simple business rule.
If the employee job='SALESMAN' then the COMMSION columns shouldnt be null.

Can this rule be enforced without using a trigger ?

regards
Hrishy
Re: Enforcing business rule [message #357445 is a reply to message #357442] Wed, 05 November 2008 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes with a check constraint.

Regards
Michel
Re: Enforcing business rule [message #357446 is a reply to message #357442] Wed, 05 November 2008 06:14 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Search for check constraint in oracle reference manual.

http://tahiti.oracle.com

Regards

Raj
Re: Enforcing business rule [message #357585 is a reply to message #357442] Wed, 05 November 2008 23:41 Go to previous messageGo to next message
hrishy
Messages: 25
Registered: August 2005
Junior Member
Hi

I am sorry i looked up the docs and googled but all the examples that came up were very simple.

Can somebody elaborate on how this can be enforced in a check constraint please.

regards
Hrishy
Re: Enforcing business rule [message #357591 is a reply to message #357442] Thu, 06 November 2008 00:12 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
So you have problem to create a condition made from two basic conditions the you posted?
The easiest way is creating the truth table based on all basic condition combinations.

In your case,
condition 1 (Cond1) is employee job='SALESMAN'
condition 2 (Cond2) is COMMSION columns is not null

Now create truth table:
Cond1 Cond2 Result
------------------
TRUE  TRUE  ?
TRUE  FALSE ?
FALSE TRUE  ?
FALSE FALSE ?

That means (question mark in e.g. 2nd row): is it acceptable when (employee job='SALESMAN') is true and (COMMSION columns is not null) is not true (= COMMSION columns is null)? If so, fill in TRUE, else FALSE. Examine it for all four rows.
In the end, use proper logical condition (AND, OR, NOT) to create one condition built from those basic conditions.
Re: Enforcing business rule [message #357593 is a reply to message #357442] Thu, 06 November 2008 00:14 Go to previous messageGo to next message
hrishy
Messages: 25
Registered: August 2005
Junior Member
Hi

Truth table is a overkill i feel i can do this easily in the front end or a pl/sql proc i was more curious of the check constraint that is declarative and codeless

regards
Hrishy
Re: Enforcing business rule [message #357597 is a reply to message #357442] Thu, 06 November 2008 00:29 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

At least show us what did you try so far.

Smile
Rajuvan.
Re: Enforcing business rule [message #357599 is a reply to message #357593] Thu, 06 November 2008 00:30 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
hrishy wrote on Thu, 06 November 2008 07:14
Truth table is a overkill i feel i can do this easily in the front end or a pl/sql proc i was more curious of the check constraint that is declarative and codeless

Truth table is just a tool for you to figure out the right condition relationship. It is just a matter of a minute or two to evaluate all four combinations.
So what is the problem? By the way, it is only logic.
Re: Enforcing business rule [message #357645 is a reply to message #357442] Thu, 06 November 2008 02:31 Go to previous messageGo to next message
hrishy
Messages: 25
Registered: August 2005
Junior Member
Hi

Well its a simple declarative statement if this can be enforeced at the db level and i am unable to do this.

The things that i have tried all involves procedural logic but i am unable to think or make it out in terms of syntax for the check constraint

alter table my_table
  add constraint ch_jobsal_has_comm 
      check (case when job='SALESMAN' 
                       or 'salesman'
                  then COMM IS NOT NULL);



but this case statement is not working
Re: Enforcing business rule [message #357652 is a reply to message #357645] Thu, 06 November 2008 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try again.
You have to express it in term of CONDITION.
Answer flyboy truth table and then create an expression that is TRUE accordingly.
hint: There are 2 conditions so there must be an AND or an OR.

Regards
Michel
Re: Enforcing business rule [message #357680 is a reply to message #357652] Thu, 06 November 2008 04:19 Go to previous messageGo to next message
hrishy
Messages: 25
Registered: August 2005
Junior Member
Hi Micheal

Please excuse my ignorenace but will the case statement even compile in oracle along with the check constraint ?

regards
Hrishy
Re: Enforcing business rule [message #357691 is a reply to message #357680] Thu, 06 November 2008 04:54 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes CASE works in a check constraint (when properly used).

Regards
Michel
Previous Topic: Simple Query that involve prefix
Next Topic: can we give variable contraining sql statment to declare a cursor
Goto Forum:
  


Current Time: Mon Dec 05 09:10:02 CST 2016

Total time taken to generate the page: 0.05490 seconds