sql - Insert into master table through integrity constraint exception handling -

db: oracle 11gr2 os: windows 7 client

hello, have master table , detail table following:

create table country (id     number not null,      code    varchar2(2) not null, creation_date    date, constraint pk_country_id primary key (code)); create table country_detail (code    varchar2(2), description varchar2(100),  creation_date    date, modify_date    date, constraint fk_country_id foreign key (code) references country(code));  insert country values(1, 'us', sysdate); insert country values(2, 'ca', sysdate); -- missing on db1 insert country values(3, 'mx', sysdate); insert country values(4, 'ch', sysdate); insert country values(5, 'in', sysdate); -- missing on db2 insert country values(6, 'jp', sysdate);  insert country_detail values('us', 'united states of ameica', sysdate, sysdate); insert country_detail values('ca', 'canada', sysdate, sysdate); -- missing on db1 insert country_detail values('mx', 'mexico', sysdate, sysdate); insert country_detail values('ch', 'peoples republic of china', sysdate, sysdate); insert country_detail values('in', 'republic of india', sysdate, sysdate); -- missing on db2 insert country_detail values('jp', 'japan', sysdate, sysdate); 

now country table exists in 2 dbs, data not same, e.g. on db1 let's 'ca' missing , on db2 'in' missing. above 6 codes can max values can reside in these tables.

now want insert these 6 records in country_detail table through (sql or pl/sql) script such way should not fail on db. i.e. on db1 when tries insert 'ca' (or on db2, insert 'in') should not fail due "integrity constraint violated - parent key not found". rather when see's error should handle in exception (for pl/sql or in kind of sql statement merge) section first inserting data in country table , insert corresponding record in country_detail table.

the task sync data on 2 databases (i.e. both environment should have 6 records in both tables).

is there way can done through kind of sql statement merge. can insert 1 one each country if it's sql statement.

if can't done in single sql how implement in pl/sql (what kind of error handling achieve end result). note, in pl/sql process data in 1 block ( i.e 1 {begin ... exception ... end;} block). how can done?

thanks

what you're asking easy enough:

merge country c using(     select  id, code, created     from(         select  1 id, 'us' code, sysdate created dual union         select  2, 'ca', sysdate dual union         select  3, 'mx', sysdate dual union         select  4, 'ch', sysdate dual union         select  5, 'in', sysdate dual union         select  6, 'jp', sysdate dual     ) ) v on( c.code = v.code ) when not matched     insert( id, code, creation_date )     values( v.id, v.code, v.created ); 

just change inline view other table appropriate.

but please allow me question: purpose of id field of country table? it's not primary key, being code field (which agree better choice). may have been meant pk, changed when code presented better of two. if such vestigial field, rid of it.