存在外键关联的主表truncate如何做

主外键是数据库提供的一种两表之间强制关联的方法,也可以从应用层实现。


优点
缺点
数据库实现的主外键
由数据库层机制保证,无需应用额外实现
强关联,不易扩展变更
应用实现的主外键
易扩展变更

完全由应用控制,要求较高

我认为需要根据实际情况进行取舍,例如表不复杂,可以由应用实现,若表之间关联较多且复杂,那么交由数据库处理,至少保证不会错。


存在主外键关联的主表,由于存在外键关联关系,因此有些操作就会禁止,例如truncate。


实验

1. 创建测试表

SQL> create table tbl_a(id number, remark varchar2(1));
Table created.

SQL> create table tbl_b(id number, a_id number, remark varchar2(1));
Table created.

SQL> alter table tbl_a add constraint pk_tbl_a primary key(id);
Table altered.

SQL> alter table tbl_b add constraint pk_tbl_b primary key(id);
Table altered.

SQL> alter table tbl_b add constraint fk_tbl_b_a foreign key(a_id) references tbl_a(id);
Table altered.

tbl_a是主表,tbl_b是子表,关联tbl_a。


2. 现在主表和子表没有任何数据,此时执行truncate主表

SQL> truncate table tbl_a;
Table truncated.
可以执行。


3. 向主表插入一条记录,再次执行truncate

SQL> insert into tbl_a values(1, 'a');
1 row created.

SQL> commit;
Commit complete.

SQL> truncate table tbl_a;
truncate table tbl_a
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

此时提示了ORA-02266:唯一/主键被启用的外键引用

看看ORA-02266的解释:

02266, 00000, "unique/primary keys in table referenced by enabled foreign keys"
// *Cause: An attempt was made to truncate a table with unique or
//         primary keys referenced by foreign keys enabled in another table.
//         Other operations not allowed are dropping/truncating a partition of a
//         partitioned table or an ALTER TABLE EXCHANGE PARTITION.
// *Action: Before performing the above operations the table, disable the
//          foreign key constraints in other tables. You can see what
//          constraints are referencing a table by issuing the following
//          command:
//          SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

比较清楚地说明了问题,以及解决方法:可以在执行前,先禁用外键约束,执行truncate后再恢复外键约束。


4. 禁用外键约束,删除后执行恢复操作

看到外键约束名称:FK_TBL_B_A:

SQL> select constraint_name, constraint_type, status from user_constraints where table_name='TBL_B';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
PK_TBL_B                       P ENABLED
FK_TBL_B_A                     R ENABLED


禁止外键约束:

SQL> alter table tbl_b disable constraint FK_TBL_B_A;

Table altered.

SQL> select constraint_name, constraint_type, status from user_constraints where table_name='TBL_B';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
PK_TBL_B                       P ENABLED
FK_TBL_B_A                     R DISABLED

STATUS状态变为DISABLED了。


truncate表:

SQL> truncate table tbl_a;

Table truncated.


恢复约束:

SQL> alter table tbl_b enable constraint FK_TBL_B_A;

Table altered.

SQL> select constraint_name, constraint_type, status from user_constraints where table_name='TBL_B';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
PK_TBL_B                       P ENABLED
FK_TBL_B_A                     R ENABLED

总结:

1. 主外键是数据库提供的强约束,可以帮助我们控制主子表之间的关系,但同时还是一把双刃剑,当然,我们认为既然定义了主外键,就是需要这种强制关系,但有时可能就会有一些变更,因此,如何取舍,需要根据实际情况来决策。

2. 主外键关联中的主表,如果有数据,则不能直接用truncate方式删除,因为会认为有外键和其关联,不能直接截断主表,若需要做,可以先禁止外键约束,主表变成一个独立的表,这样就可以执行truncate了。



©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值