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?

  • How did you end up implementing this? – Nick Chammas Oct 13 '11 at 20:46

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

  • 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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