set feedback off
drop table t3 purge;
drop table t2 purge;
drop table t1 purge;
create table t1(x int, object_name varchar2(30),
constraint t1_pk primary key(x) );
create table t2(x int, y int, object_name varchar2(30),
constraint t2_pk primary key(x) ,
constraint t2_fk foreign key(y) references t1 on delete cascade);
create index t2_idx on t2(y);
create table t3(x int, y int,
constraint t3_pk primary key(x,y))
organization index ;
create or replace trigger t2_trig
before insert or delete on t2
for each row
begin
if inserting then
insert into t3(x,y) values(:new.x,:new.y) ;
elsif deleting then
delete from t3 where x = :old.x and y = :old.y;
end if;
end;
/
insert into t1 values(1,'Text1');
insert into t1 values(2,'Text2');
insert into t2 values(3,1,'Text1');
insert into t2 values(4,2,'Text2');
commit;
set feedback on
In case of non-partitioned Table, looks OK.
demo@ORA12C>
demo@ORA12C> select * from t1;
X OBJECT_NAME
---------- ------------------------------
1 Text1
2 Text2
2 rows selected.
demo@ORA12C> select * from t2;
X Y OBJECT_NAME
---------- ---------- ------------------------------
3 1 Text1
4 2 Text2
2 rows selected.
demo@ORA12C> select * from t3;
X Y
---------- ----------
3 1
4 2
2 rows selected.
demo@ORA12C> delete from t1
2 where t1.x in ( select t2.y from t2
3 where t2.x in ( select x from t3
4 where y = 2) );
1 row deleted.
demo@ORA12C> select * from t1;
X OBJECT_NAME
---------- ------------------------------
1 Text1
1 row selected.
demo@ORA12C> select * from t2;
X Y OBJECT_NAME
---------- ---------- ------------------------------
3 1 Text1
1 row selected.
demo@ORA12C> select * from t3;
X Y
---------- ----------
3 1
1 row selected.
demo@ORA12C>
In case of partitioned Tables.
set feedback off
drop table t3 purge;
drop table t2 purge;
drop table t1 purge;
create table t1(x int, object_name varchar2(30) )
partition by hash(x)
partitions 4;
alter table t1 add constraint t1_pk
primary key(x) using index (create unique index t1_pk
on t1(x) local );
create table t2(x int, y int, object_name varchar2(30))
partition by hash(x)
partitions 4;
alter table t2 add constraint t2_pk
primary key(x) using index ( create unique index t2_pk
on t2(x) local );
create index t2_idx on t2(y);
alter table t2 add constraint t2_fk
foreign key(y) references t1
on delete cascade;
create table t3(x int, y int,
constraint t3_pk primary key(x,y))
organization index ;
create or replace trigger t2_trig
before insert or delete on t2
for each row
begin
if inserting then
insert into t3(x,y) values(:new.x,:new.y) ;
elsif deleting then
delete from t3 where x = :old.x and y = :old.y;
end if;
end;
/
insert into t1 values(1,'Text1');
insert into t1 values(2,'Text2');
insert into t2 values(3,1,'Text1');
insert into t2 values(4,2,'Text2');
commit;
set feedback on
look problem now.
demo@ORA12C> select * from t1;
X OBJECT_NAME
---------- ------------------------------
2 Text2
1 Text1
2 rows selected.
demo@ORA12C> select * from t2;
X Y OBJECT_NAME
---------- ---------- ------------------------------
3 1 Text1
4 2 Text2
2 rows selected.
demo@ORA12C> select * from t3;
X Y
---------- ----------
3 1
4 2
2 rows selected.
demo@ORA12C> delete from t1
2 where t1.x in ( select t2.y from t2
3 where t2.x in ( select x from t3
4 where y = 2) );
1 row deleted.
demo@ORA12C> select * from t1;
X OBJECT_NAME
---------- ------------------------------
1 Text1
1 row selected.
demo@ORA12C> select * from t2;
X Y OBJECT_NAME
---------- ---------- ------------------------------
3 1 Text1
4 2 Text2
2 rows selected.
demo@ORA12C> select * from t3;
X Y
---------- ----------
3 1
4 2
2 rows selected.
demo@ORA12C>
demo@ORA12C> commit;
Commit complete.
demo@ORA12C>
The strange thing is even during commit, constraint is not validated and no errors.
Able to reproduce this behavior in 11g(11.2.0.4) and 12c(12.1.0.2) but not with 10g(10.2.0.5)
demo@ORA10G> set feedback off
demo@ORA10G> drop table t3 purge;
demo@ORA10G> drop table t2 purge;
demo@ORA10G> drop table t1 purge;
demo@ORA10G> create table t1(x int, object_name varchar2(30) )
2 partition by hash(x)
3 partitions 4;
demo@ORA10G> alter table t1 add constraint t1_pk
2 primary key(x) using index (create unique index t1_pk
3 on t1(x) local );
demo@ORA10G> create table t2(x int, y int, object_name varchar2(30))
2 partition by hash(x)
3 partitions 4;
demo@ORA10G> alter table t2 add constraint t2_pk
2 primary key(x) using index ( create unique index t2_pk
3 on t2(x) local );
demo@ORA10G> create index t2_idx on t2(y);
demo@ORA10G> alter table t2 add constraint t2_fk
2 foreign key(y) references t1
3 on delete cascade;
demo@ORA10G> create table t3(x int, y int,
2 constraint t3_pk primary key(x,y))
3 organization index ;
demo@ORA10G> create or replace trigger t2_trig
2 before insert or delete on t2
3 for each row
4 begin
5 if inserting then
6 insert into t3(x,y) values(:new.x,:new.y) ;
7 elsif deleting then
8 delete from t3 where x = :old.x and y = :old.y;
9 end if;
10 end;
11 /
demo@ORA10G> insert into t1 values(1,'Text1');
demo@ORA10G> insert into t1 values(2,'Text2');
demo@ORA10G> insert into t2 values(3,1,'Text1');
demo@ORA10G> insert into t2 values(4,2,'Text2');
demo@ORA10G> commit;
demo@ORA10G> set feedback on
demo@ORA10G>
demo@ORA10G>
demo@ORA10G> select * from t1;
X OBJECT_NAME
---------- ------------------------------
2 Text2
1 Text1
2 rows selected.
demo@ORA10G> select * from t2;
X Y OBJECT_NAME
---------- ---------- ------------------------------
3 1 Text1
4 2 Text2
2 rows selected.
demo@ORA10G> select * from t3;
X Y
---------- ----------
3 1
4 2
2 rows selected.
demo@ORA10G> delete from t1
2 where t1.x in ( select t2.y from t2
3 where t2.x in ( select x from t3
4 where y = 2) );
1 row deleted.
demo@ORA10G> select * from t1;
X OBJECT_NAME
---------- ------------------------------
1 Text1
1 row selected.
demo@ORA10G> select * from t2;
X Y OBJECT_NAME
---------- ---------- ------------------------------
3 1 Text1
1 row selected.
demo@ORA10G> select * from t3;
X Y
---------- ----------
3 1
1 row selected.
demo@ORA10G> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
5 rows selected.
demo@ORA10G>