Home » SQL & PL/SQL » SQL & PL/SQL » enforcing rules
enforcing rules [message #400570] Tue, 28 April 2009 19:05 Go to next message
LittleB
Messages: 8
Registered: April 2009
Junior Member
Hello All!

I wonder if someone can help. I have a table that consists of 3 columns: aisle, Shelf and class.

How do I enforce a rule such that values for both aisle and Shelf determine the value for class, e.g.
if aisle = 'front', Shelf<='10' then class = 'A',
if aisle = 'front', Shelf>'10' then class = 'B', etc.

Many Thanks!!
Re: enforcing rules [message #400571 is a reply to message #400570] Tue, 28 April 2009 19:15 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
What if aisle <> 'front?
A trigger will do what you desire.

You need to help us by following the Posting Guidelines as stated below.

http://www.orafaq.com/forum/t/88153/0/

[Updated on: Tue, 28 April 2009 19:16]

Report message to a moderator

Re: enforcing rules [message #400576 is a reply to message #400571] Tue, 28 April 2009 22:05 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You could probably also do it with CHECK constraints

Ross Leishman
Re: enforcing rules [message #400643 is a reply to message #400570] Wed, 29 April 2009 03:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you can derive class from the other two, you should probably not store it in the table.
Re: enforcing rules [message #400662 is a reply to message #400570] Wed, 29 April 2009 04:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I recommend you to read Normalization page and try to fit 3NF.

Regards
Michel
Re: enforcing rules [message #400703 is a reply to message #400662] Wed, 29 April 2009 05:43 Go to previous messageGo to next message
LittleB
Messages: 8
Registered: April 2009
Junior Member
Hello again and thanks for all your replies and suggestions.

What I wanted to say is that the value of 1 of the 3 columns depends on the values in the other 2.

So depending on what aisle and what shelf number, I want the database to work out what class the item should be, or at least check the right class has been entered.

I think class here is fully functionally and not transitively dependent on the other 2 columns. Or am I wrong?

Is that still 'CHECK' constraint or can it be done some other way?

Many Thanks!!
Re: enforcing rules [message #400720 is a reply to message #400703] Wed, 29 April 2009 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What I wanted to say is that the value of 1 of the 3 columns depends on the values in the other 2.

What we wanted to say is that it should not if you normalize your model.
If you really want this, use 11g and a virtual column.

Quote:
I think class here is fully functionally and not transitively dependent on the other 2 columns. Or am I wrong?

What does this mean? Define functionnaly dependent and transively dependent.

Regards
Michel
Re: enforcing rules [message #400768 is a reply to message #400703] Wed, 29 April 2009 07:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
LittleB wrote on Wed, 29 April 2009 12:43
What I wanted to say is that the value of 1 of the 3 columns depends on the values in the other 2.
...
I think class here is fully functionally and not transitively dependent on the other 2 columns. Or am I wrong?


Isn't this contradicting?
Re: enforcing rules [message #400801 is a reply to message #400768] Wed, 29 April 2009 12:20 Go to previous messageGo to next message
LittleB
Messages: 8
Registered: April 2009
Junior Member
Hello again!!

I am sorry if I'm contradicting myself, may be it's because I've been up for so many hours and can't think straight.

About the 'aisle' and 'shelf' columns, I forgot to mention that they're the primary key (composite key) in that table.

I think Frank has said earlier that the 'class' attribute can be derived, I don't know how to work on that since I'm not that experienced with SQL. I will fiddle around with it but if I'm unsuccessful I hope you won't mind hearing from me Razz

Thanks!!
Re: enforcing rules [message #400803 is a reply to message #400801] Wed, 29 April 2009 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read the link I posted?
Did you CAREFULLY read and understand our posts?
What is said is: you don't need and shouldn't have this column (unless it is a virtual one).

Regards
Michel
Re: enforcing rules [message #400885 is a reply to message #400801] Thu, 30 April 2009 04:33 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
LittleB wrote on Wed, 29 April 2009 19:20
I am sorry if I'm contradicting myself, may be it's because I've been up for so many hours and can't think straight.

Hehe.. we've all been there Smile

Quote:
I think Frank has said earlier that the 'class' attribute can be derived, I don't know how to work on that since I'm not that experienced with SQL.

Your initial plan was to create a rule that would enforce that the value of CLASS would be x, depending on the values of the other two.
Now, if you can think up that rule, in a way that you can always determine a single allowed value for CLASS for each combination of the other two columns, that means that the CLASS value can be derived from the other two.
Then, according to normalisation-rules, there is no need to store that value in the db, as it can always be calculated.

If this really is the case, first try to write down the rule in english.
Then translate that to pseudo-code
Then to SQL

If you fail in any step (but the first Smile), post what you already did & tried.
Previous Topic: Global Variable
Next Topic: how oracle update query works internaly
Goto Forum:
  


Current Time: Wed Dec 07 10:46:44 CST 2016

Total time taken to generate the page: 0.11487 seconds