Who cares if what you’ve got is wrong, just make it right from now on SQL

create table parent_tab ( 
  pk integer primary key 
create table i_know_my_data_is_rubbish_so ( 
  parent_fk integer , 
  -- create the constraint disabled, 
  -- so we're not enforcing it
  constraint so_at_least_new_stuff_is_good 
  foreign key (parent_fk) references parent_tab disable

--there's nothing in the parent table, 
--so the FK can't reference it
--but because the constraint is disabled 
--we're not checking this
insert into i_know_my_data_is_rubbish_so 
values (1);


-- if we enable the constraint with validation, 
-- (the default) it fails because there's no
-- parent keys
alter table i_know_my_data_is_rubbish_so 
enable constraint so_at_least_new_stuff_is_good;

-- enabling novalidate stops checking the row 
-- that's already there so the constraint it 
-- now enforced (depsite having data violating 
-- it)
alter table i_know_my_data_is_rubbish_so 
enable novalidate 
constraint so_at_least_new_stuff_is_good;

--but new changes fail the constraint
insert into i_know_my_data_is_rubbish_so 
values (1);

-- this can also be used with other types of 
-- constraint: primary, unique, check, etc.

drop table i_know_my_data_is_rubbish_so purge;
drop table parent_tab 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>