Solved

Simple Domain Constraints

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle function works in 11g but not in 12c 21 72
PL/SQL - Leading zeros 7 60
SQL Query 34 82
How do I call MySQL Stored Procedure from oracle using HS link ? 5 27
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…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.

895 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

12 Experts available now in Live!

Get 1:1 Help Now