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
Solved

Simple Domain Constraints

Posted on 2003-12-05
6
883 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 50 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

856 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