Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Simple Domain Constraints

Posted on 2003-12-05
6
Medium Priority
?
888 Views
Last Modified: 2008-03-06
Hello,

Suppose I have simplified,

Items_details(item_id, color)

What is the best method to enforce color is valid.

1. Check constraint
2. Trigger
3. Seperate table containing a single column 'color'

Are seperate single column tables used, suppose you want to record configuration settings for your web site, containing 50+ parameters.

Thanks.
0
Comment
Question by:robert_83
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 

Author Comment

by:robert_83
ID: 9883343
"suppose you want to record configuration settings for your web site, containing 50+ parameters." <- I meant tables with no relationship.
0
 
LVL 15

Expert Comment

by:andrewst
ID: 9884224
Well, the most efficient way would be the check constraint.  But if you want users to be able to specify new colours later through the application, then it may be preferable to have a table of colours and a foreign key from the item_details table to the colours table.

I would never use a trigger to enforce such validation - that would be inefficient.
0
 

Author Comment

by:robert_83
ID: 9885842
The table for color would essentially be single columned or at most have another column for id, I suppose this is ok, as it provides an efficent method to check values.

The check constraint I assume would be where color in 'x', ... 'y'

The trigger I also believed would the most inefficent because it checks all rows, after every update / insert on color?

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 15

Expert Comment

by:andrewst
ID: 9887835
Yes, a single-columned table is OK, and the check constraint would be as you say.

A FOR EACH ROW trigger does not check all rows, only the rows being inserted or updated.  It is less efficient simply because it is procedural PL/SQL code that is run for each record, rather than a declarative constraint which can be performed more optimally.  Only use triggers for validation or action that cannot be performed via a check, foreign key or unique constraint.
0
 

Author Comment

by:robert_83
ID: 9887977
Cheers,

If the only colors availible were say between 4 - 10. Would you prefer a single row table, or check (). I personally believe the table is better, though only if colors are likely to be updated in the future.
0
 
LVL 15

Accepted Solution

by:
andrewst earned 200 total points
ID: 9888219
I imagine colours are highly likely to be added in the future, so would probably go for the table.  But here we are getting into business-specific decisions rather than technical ones.  But I would only use a check constraint for domains that I didn't expect to change over time.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question