oracle联级删除
一个demo
方便记忆
1 SQL> create table t1 2 2 ( 3 3 id varchar2(10), 4 4 name varchar2(10) 5 5 ); 6 7 Table created 8 9 SQL> create table t2 10 2 ( 11 3 id varchar2(10), 12 4 pid varchar2(10), 13 5 name varchar2(10) 14 6 ); 15 16 Table created 17 18 SQL> 19 SQL> create table t3 20 2 ( 21 3 id varchar2(10), 22 4 pid varchar2(10), 23 5 name varchar2(10) 24 6 ); 25 26 Table created 27 28 SQL> alter table t1 29 2 add constraint pk_t1 primary key (id); 30 31 Table altered 32 33 SQL> 34 SQL> alter table t2 35 2 add constraint pk_t2 primary key (id); 36 37 Table altered 38 39 SQL> 40 SQL> alter table t3 41 2 add constraint pk_t3 primary key (id); 42 43 Table altered 44 45 SQL> alter table t2 add constraint FK_t1_t2 foreign key (pid) references t1(id) on delete cascade not deferrable; 46 47 Table altered 48 49 SQL> alter table t3 add constraint FK_t2_t3 foreign key (pid) references t2(id) on delete cascade not deferrable; 50 51 Table altered 52 53 SQL> insert into t1 values('1','aaa'); 54 55 1 row inserted 56 57 SQL> insert into t1 values('2','bbb'); 58 59 1 row inserted 60 61 SQL> insert into t2 values ('1','1','mmm'); 62 63 1 row inserted 64 65 SQL> insert into t2 values ('2','1','nnn'); 66 67 1 row inserted 68 69 SQL> insert into t3 values ('1','1','xxx'); 70 71 1 row inserted 72 73 SQL> commit; 74 75 Commit complete 76 77 SQL> select * from t1; 78 79 ID NAME 80 ---------- ---------- 81 1 aaa 82 2 bbb 83 84 SQL> select * from t2; 85 86 ID PID NAME 87 ---------- ---------- ---------- 88 1 1 mmm 89 2 1 nnn 90 91 SQL> select * from t3; 92 93 ID PID NAME 94 ---------- ---------- ---------- 95 1 1 xxx 96 97 SQL> delete t1 where id = '1'; 98 99 1 row deleted 100 101 SQL> commit; 102 103 Commit complete 104 105 SQL> select * from t1; 106 107 ID NAME 108 ---------- ---------- 109 2 bbb 110 111 SQL> select * from t2; 112 113 ID PID NAME 114 ---------- ---------- ---------- 115 116 SQL> select * from t3; 117 118 ID PID NAME 119 ---------- ---------- ---------- 120 121 SQL> rollback; 122 123 Rollback complete
