0

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

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Barry Brierley
  • 27
  • 7
  • I'm don't have a problem with a unique constraint, I have a problem with a check constraint – Barry Brierley Oct 23 '20 at 11:04
  • . . Your question is quite unclear. You seem to have a question on a check constraint, but you start with an irrelevant function. Sample data, desired results -- as text tables -- and showing the code that generates the error would make the question clearer. – Gordon Linoff Oct 23 '20 at 11:17
  • How is my function irrelevent? It's used by my virtual column which I'm trying to reference in my constraint. My problem, as per the title is "constraint on a virtual column". Update any row where there is another unit_ref_Code with "Y" and it will allow you to, despite my virtual column now showing 2 (see original screenshot), despite my constraint which should not allow > 1. UPDATE UNITS SET APPLY_CONVERSION_IND = 'Y' WHERE ROWID = 'AABgbbAASAAAAK+AAB' – Barry Brierley Oct 23 '20 at 11:39
  • I haven't debugged it to see but declaring a function `deterministic` means that you're assuring Oracle that for the same input, you'll always get the same output. If you're querying another table that can be modified, the function is not deterministic which would prevent you from using it to compute a virtual column. It seems like you just want a function-based unique index instead. Is that an option or is there a reason you need the virtual column? – Justin Cave Oct 23 '20 at 12:03

1 Answers1

1

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 );
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thank you Justin. I think your answer is correct. I've also been given `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 );` – Barry Brierley Oct 23 '20 at 12:24
  • 1
    The virtual column and constraint suggestion by Wernfried was probably a red herring: https://stackoverflow.com/questions/16778948/check-constraint-calling-a-function-oracle-sql-developer The answer to my query of why the constraint did't work is probably "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." – Barry Brierley Oct 23 '20 at 12:31