Conditional integrity SQL

 
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;

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)