oracle级联删除

oracle级联删除

问题：有三张表，分别是省、市、县，如果要在省表删除一个具体省，则该省下所有的省市和县都删除掉；如果在市表中删除一个具体市，该城市下的所有县也将删除掉。

解决办法：在被级联的表设定外连接，并指定on delete的值（有“on delete cascade”和“on delete set null”作为值）

具体说明：

省表字段

PRO_ID	VARCHAR2(20)	省份编号			省份编号
PRO_NAME	VARCHAR2(30)	省份名称			省份名称
PRO_INDEX	NUMBER(18,4)	排序			排序
PRO_P_ID	VARCHAR2(20)	省份父编号（数据操作时使用）			省份父编号（数据操作时使用）

市表字段、外连接和级联删除的设定

PRO_ID	VARCHAR2(20)	省份编号
CITY_ID	VARCHAR2(20)	市编号
CITY_NAME	VARCHAR2(50)	市名称
CITY_INDEX	NUMBER(18,4)	排序

 

县表字段、外连接和级联删除的设定

PRO_ID	VARCHAR2(20)	省份编号
CITY_ID	VARCHAR2(20)
TOWN_ID	VARCHAR2(20)
TOWN_NAME	VARCHAR2(60)
TOWN_INDEX	NUMBER(18,4)

 

注意点

上面表Referencing colums 的字段必须是被连接的表的主键字段。

 

 

建表语句

省：

View Code

-- Create table
create table T_BASE_PROVINCE
(
  PRO_ID    VARCHAR2(20) not null,
  PRO_NAME  VARCHAR2(30),
  PRO_INDEX NUMBER(18,4),
  PRO_P_ID  VARCHAR2(20)
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 8K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column T_BASE_PROVINCE.PRO_ID
  is '省份编号';
comment on column T_BASE_PROVINCE.PRO_NAME
  is '省份名称';
comment on column T_BASE_PROVINCE.PRO_INDEX
  is '排序';
comment on column T_BASE_PROVINCE.PRO_P_ID
  is '省份父编号（数据操作时使用）';
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_BASE_PROVINCE
  add constraint PK_BASE_PROID primary key (PRO_ID)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

 

市

View Code

 1 -- Create table
 2 create table T_BASE_CITY
 3 (
 4   PRO_ID     VARCHAR2(20),
 5   CITY_ID    VARCHAR2(20) not null,
 6   CITY_NAME  VARCHAR2(50),
 7   CITY_INDEX NUMBER(18,4)
 8 )
 9 tablespace SYSTEM
10   pctfree 10
11   pctused 40
12   initrans 1
13   maxtrans 255
14   storage
15   (
16     initial 64K
17     next 8K
18     minextents 1
19     maxextents unlimited
20   );
21 -- Add comments to the columns 
22 comment on column T_BASE_CITY.PRO_ID
23   is '省份编号';
24 comment on column T_BASE_CITY.CITY_ID
25   is '市编号';
26 comment on column T_BASE_CITY.CITY_NAME
27   is '市名称';
28 comment on column T_BASE_CITY.CITY_INDEX
29   is '排序';
30 -- Create/Recreate primary, unique and foreign key constraints 
31 alter table T_BASE_CITY
32   add constraint PK_BASE_CITYID primary key (CITY_ID)
33   using index 
34   tablespace SYSTEM
35   pctfree 10
36   initrans 2
37   maxtrans 255
38   storage
39   (
40     initial 64K
41     next 1M
42     minextents 1
43     maxextents unlimited
44   );
45 alter table T_BASE_CITY
46   add constraint FK_CITY_PROVINCE foreign key (PRO_ID)
47   references T_BASE_PROVINCE (PRO_ID) on delete cascade;
48 -- Create/Recreate indexes 
49 create index IDX_CITYPROVINCE on T_BASE_CITY (PRO_ID)
50   tablespace SYSTEM
51   pctfree 10
52   initrans 2
53   maxtrans 255
54   storage
55   (
56     initial 64K
57     next 1M
58     minextents 1
59     maxextents unlimited
60   );

县

View Code

 1 -- Create table
 2 create table T_BASE_TOWN
 3 (
 4   PRO_ID     VARCHAR2(20),
 5   CITY_ID    VARCHAR2(20),
 6   TOWN_ID    VARCHAR2(20) not null,
 7   TOWN_NAME  VARCHAR2(60),
 8   TOWN_INDEX NUMBER(18,4)
 9 )
10 tablespace SYSTEM
11   pctfree 10
12   pctused 40
13   initrans 1
14   maxtrans 255
15   storage
16   (
17     initial 64K
18     next 8K
19     minextents 1
20     maxextents unlimited
21   );
22 -- Add comments to the table 
23 comment on table T_BASE_TOWN
24   is '县/市/区';
25 -- Add comments to the columns 
26 comment on column T_BASE_TOWN.PRO_ID
27   is '省份编号';
28 -- Create/Recreate primary, unique and foreign key constraints 
29 alter table T_BASE_TOWN
30   add constraint PK_BASE_TOWNID primary key (TOWN_ID)
31   using index 
32   tablespace SYSTEM
33   pctfree 10
34   initrans 2
35   maxtrans 255
36   storage
37   (
38     initial 64K
39     next 1M
40     minextents 1
41     maxextents unlimited
42   );
43 alter table T_BASE_TOWN
44   add constraint FK_TOWN_CITY foreign key (CITY_ID)
45   references T_BASE_CITY (CITY_ID) on delete cascade;
46 alter table T_BASE_TOWN
47   add constraint FK_TOWN_PROVINCE foreign key (PRO_ID)
48   references T_BASE_PROVINCE (PRO_ID) on delete cascade;
49 -- Create/Recreate indexes 
50 create index IDX_TOWNCITY on T_BASE_TOWN (CITY_ID)
51   tablespace SYSTEM
52   pctfree 10
53   initrans 2
54   maxtrans 255
55   storage
56   (
57     initial 64K
58     next 1M
59     minextents 1
60     maxextents unlimited
61   );
62 create index IDX_TOWNPROVINCE on T_BASE_TOWN (PRO_ID)
63   tablespace SYSTEM
64   pctfree 10
65   initrans 2
66   maxtrans 255
67   storage
68   (
69     initial 64K
70     next 1M
71     minextents 1
72     maxextents unlimited
73   );

 

Oracle

    oracle中使用on delete cascade和on delete set null来建立外键

参考 http://cesium.iteye.com/blog/599516