Solved

Simple Domain Constraints

Posted on 2003-12-05
6
882 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now