Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a column RSEFlag that contains a flag value that is the sum of one or more of 1, 2, 4. I have separate table that contains the description of each of those flag values:

RSECode RSEDescription
------- --------------
   4    Environment
   1    Reliability
   2    Safety

I would like to constrain my RSEFlag column so that it is less than or equal to the sum of values from the RSE table.

Is this a worthwhile exercise and what is the best approach?

share|improve this question
    
How did you end up implementing this? –  Nick Chammas Oct 13 '11 at 20:46

2 Answers 2

  • You can use a CHECK constraint defined against a UDF to enforce this, but this comes with many caveats.
  • You can also define INSERT and UPDATE triggers to do this same work.
  • In Oracle, you could create a materialized view that joined the two tables together and placed your RSEFlag column next to the appropriate sum. Then you would apply a CHECK constraint to enforce that RSEFlag <= RSECode_SUM. This Ask Tom discussion has an example of how to do this. (Search for alter table dept_tot_mvw add constraint and note that dept_tot_mvw is a materialized view.)

    Unfortunately, you cannot use this approach since constraints are not allowed on indexed views in SQL Server.

share|improve this answer
    
The materialized view solution for Oracle has it's own problems in my view - because the MV only refreshes when you commit. –  Jack Douglas Sep 28 '11 at 11:38
1  
I should have said "in my opinion" instead of "in my view" :-) –  Jack Douglas Sep 28 '11 at 16:42

Is this a worthwhile exercise and what is the best approach?

IMO this it is not a worthwhile exercise to try and enforce this kind of constraint with DRI because of the concurrency problems alone associated with the UDF and trigger methods of constraining the data - and there are other caveats too as Nick mentions

Of course you can 'enforce' this kind of constraint in a transactional API - see here for my answer to a related question for details of what I mean

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.