Using constraints to enforce data integrity

Problem

I needed to enforce the fact that I could only have 1 row set to "Y" for APPLY_CONVERSION_IND for each UNIT_REF_CODE.

UNIT_REF_CODE                APPLY_CONVERSION_IND

****************                *************************

LENGTH                                Y

LENGTH                                N

LENGTH                                N

WIDTH                                   Y

WIDTH                                    N

WIDTH                                    Y

I could not do this through the application - it therefore had to be caught on the database.

What didn't work

https://stackoverflow.com/questions/16778948/check-constraint-calling-a-function-oracle-sql-developer

It was interesting to play with this logic, by creating a virtual column, functions and a constraint.

ALTER TABLE UNITS

ADD CONSTRAINT UNITS_APPLY_CONVERSION_IND_Cons CHECK(UNITS_APPLY_CONVERSION_IND_Count <= 1);

But this turned into a red herring becasue "virtual columns are calculated as you query them (the deterministic part helps to cache values) so shouldn't really be used in an attempt at a constraint. The value is indeterminate at the point you choose to insert/update."

I briefly explored using triggers, but as I would need to select from the same table that I was changing I ran into a mutating table issue:

Solution

This neat little constraint uses a case statement:

CREATE UNIQUE INDEX UNITS_IDX1

   ON UNITS(

             CASE APPLY_CONVERSION_IND

               WHEN 'Y' THEN UNITS_REF_CODE

             END,

            CASE APPLY_CONVERSION_IND

               WHEN 'Y' THEN 'Y'

             END

            );

or

create unique index idx_one_units_ref_code
    on units( case when apply_conversion_ind = 'Y'
                   then units_ref_code
                   else null
               end );

Acknowledgement

https://community.oracle.com/tech/developers/discussion/4476655/constraint-on-a-virtual-column/p1?new=1

https://stackoverflow.com/questions/64498366/oracle-sql-constraint-on-virtual-column