Add constraint to ensure flag column is <= sum of column in other table

Question

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?

score 4 · Answer 1 · edited Apr 13 '17 at 12:42

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.

score 2 · Accepted Answer · edited Apr 13 '17 at 12: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

Add constraint to ensure flag column is <= sum of column in other table

2 Answers2