Open a SQL Plus, create the tables and foreign key.
create table tmp_test ( id number(5) not null, test_type char(1), comments varchar2(100), constraint pk_tmp_test primary key (id) ); create table tmp_test_type ( test_type char(1), description varchar2(100), constraint pk_tmp_test_type primary key (test_type) ); alter table tmp_test add constraint fk1_tmp_test foreign key ( test_type ) references tmp_test_type;
Now try to insert a row without referring type. Expecting an error:
insert into tmp_test values( 1, 'A', 'Just a test' ); ORA-02291: integrity constraint (EAS_DEMO.FK1_TMP_TEST) violated - parent key not found
Open another session using SQL Plus, insert a row in test_test_type table but don't commit:
insert into tmp_test_type values( 'A', 'Test type A' );
Run the insert again using the first SQL Plus, you'll find it hangs. In Oracle Enterprise Manager, you can see "enq: TX - row lock contention".
Commit the changes and cleanup:
drop table tmp_test; drop table tmp_test_type;