CODEWITHSUNDEEP
sqloracle-database
Barry Brierley
Barry Brierley

Reputation: 27

Oracle SQL: Constraint on virtual column

Any idea why this constraint appears not to be firing?

CREATE OR REPLACE FUNCTION UNITS_APPLY_CONVERSION_IND_CHECK (P_UNITS_REF_CODE VARCHAR2) RETURN NUMBER DETERMINISTIC IS

v_cnt NUMBER;

BEGIN


  SELECT Count(*)
  INTO  v_cnt
  FROM  wms.units
  WHERE units_ref_code = P_UNITS_REF_CODE
  AND apply_conversion_ind = 'Y';

  RETURN v_cnt;

END;

then

ALTER TABLE UNITS ADD (UNITS_APPLY_CONVERSION_IND_Count NUMBER GENERATED ALWAYS AS (UNITS_APPLY_CONVERSION_IND_CHECK(units_ref_code)) VIRTUAL);

then

ALTER TABLE UNITS
ADD CONSTRAINT UNITS_APPLY_CONVERSION_IND_Cons CHECK(UNITS_APPLY_CONVERSION_IND_Count <= 1);

Yet, with the above, I'm able to update any row I want to cause my virtual column to go above 1 - the constraint does not stop me. Any ideas?

enter image description here

Upvotes: 0

Views: 748

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

It looks like you are just trying to ensure that units_ref_code is unique in the table when apply_conversion_ind = 'Y'. Assuming that's the case, you'd just need a function-based unique index. You wouldn't need a function or a virtual column.

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

Upvotes: 2

Related Questions