create table to_this_table (when_it_equals_other_vals integer primary key);
create table conditional_integrity ( this_col varchar2(100), allowing_this_col_to_have_no integer );
alter table conditional_integrity add (
a_col_only_populated as (case when this_col = 'SOMETHING SPECIAL' then
allowing_this_col_to_have_no end)
references to_this_table (when_it_equals_other_vals) );
insert into to_this_table values (1);
PRO fails - column has special value and PK not present in target table
insert into conditional_integrity ( this_col, allowing_this_col_to_have_no)
values ('SOMETHING SPECIAL', 2);
PRO works - column has special value, but PK is present in target table
insert into conditional_integrity ( this_col, allowing_this_col_to_have_no)
values ('SOMETHING SPECIAL', 1);
PRO works - column doesn't have special value, so our new col is null => RI isn't applied
insert into conditional_integrity ( this_col, allowing_this_col_to_have_no)
values ('ANYTHING ELSE', 2);
drop table conditional_integrity purge;
drop table to_this_table purge;