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;
No comments:
Post a Comment