Solved

Simple Domain Constraints

Posted on 2003-12-05
6
884 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MULTIPLE DATE QUERY 15 103
Database Design Dilemma 6 66
How can track error data while copeing data from external file to database using utl_file. 5 30
Excess Redo 3 32
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
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

726 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