primary key & foreign key 研究（一）

目的：

这次做表空间清理的时候，碰到需要清理的A表建有主键，不需要清理的B表建有和A表主键关联的外键。将A表强制truncate后，导致B表的外键disabled，着实吓得不轻，此处暴露了wasd我对主键、外键使用的模糊，于是决定做实验来弄明白它们的使用关系。

准备：

数据库oracle9i，因为我的测试库只有三个表空间SYSTEM，UNDOTBS1，TEMP三个，所以我测试用的是sys用户，空间为system表空间。

---首先建表–为了方便将两个表建成一致的表结构。

create table wasd_01(

wasd_id number,

wasd_name varchar2(10),

wasd_date date);

 

create table wasd_02(

wasd_id number,

wasd_name varchar2(10),

wasd_date date);

---插入初步测试数据

Insert into wasd_01 values(1,'wasd1',sysdate);

Insert into wasd_01 values(2,'wasd2',sysdate);

Insert into wasd_01 values(3,'wasd3',sysdate);

Insert into wasd_02 values(1,'wasd1',sysdate);

Insert into wasd_02 values(2,'wasd2',sysdate);

Insert into wasd_02 values(3,'wasd3',sysdate-1);

--给两个表赋予约束

SQL> alter table wasd_01 add (constraint pk_wasd_01 primary key (wasd_id,wasd_name)

 2 using index );

 Table altered

 SQL>

SQL> alter table wasd_02 add(constraint fk_wasd_02 foreign key (wasd_id,wasd_name)

 2 references wasd_01(wasd_id,wasd_name)) ;

Table altered

 

重现问题与解决：

将wasd_01做truncate；

SQL> truncate table wasd_01;

 

truncate table wasd_01

ORA-02266:表中的唯一/主键被启用的外部关键字引用

下面强制truncate表wasd_01

SQL> alter table wasd_01 disable primary key cascade ;

Table altered

SQL> truncate table wasd_01;

Table truncated

SQL> alter table wasd_01 enable primary key ;

Table altereds

SQL> select constraint_name,status from user_constraints where table_name like 'WASD_%';

CONSTRAINT_NAME               STATUS

------------------------------ --------

PK_WASD_01                    ENABLED

FK_WASD_02                    DISABLED—这时wasd_02的外键已经失效了

 

SQL> alter table wasd_02 enable constraint fk_wasd_02;

alter table wasd_02 enable constraint fk_wasd_02

ORA-02298:无法验证(SYS.FK_WASD_02) -未找到父项关键字

如何来恢复呢？？如何让wasd_02表的外键可用呢？？

既然是在“未找到父项关键字”，那么我们就给它给“未找到父项关键字”：

SQL> Insert into wasd_01 values(1,'wasd1',sysdate);

1 row inserted

SQL> commit;

Commit complete

SQL> alter table wasd_02 enable constraint fk_wasd_02;

alter table wasd_02 enable constraint fk_wasd_02

ORA-02298:无法验证(SYS.FK_WASD_02) -未找到父项关键字

看来需要赋给其所有的“父项关键字”，继续：

SQL> Insert into wasd_01 values(2,'wasd2',sysdate-3);--注意这个地方

1 row inserted

SQL> Insert into wasd_01 values(3,'wasd3',sysdate);

1 row inserted

SQL> commit;

Commit complete

SQL> alter table wasd_02 enable constraint fk_wasd_02;

Table altered

SQL> select constraint_name,status from user_constraints where table_name like 'WASD_%';

 

CONSTRAINT_NAME               STATUS

------------------------------ --------

PK_WASD_01                    ENABLED

FK_WASD_02                    ENABLED

至此恢复成功。恢复的要点就是让wasd_02外键字段有的数据wasd_01也要有，如果直接做truncate table wasd_02，当然也是可以恢复外键的。

其他实验：

对wasd_01进行dml操作

SQL> delete from wasd_01 where wasd_id=1;

delete from wasd_01 where wasd_id=1

ORA-02292:违反完整约束条件(SYS.FK_WASD_02) -已找到子记录日志—说明要删除wasd_01和wasd_02关联的数据会导致外键失效

SQL> insert into wasd_01 values (5,'wasd5',sysdate);

1 row inserted

SQL> rollback;

Rollback complete—-说明wasd_01有的wasd_02没有是可以的

SQL> update wasd_01 set wasd_id=6 where wasd_id=1;

update wasd_01 set wasd_id=6 where wasd_id=1

ORA-02292:违反完整约束条件(SYS.FK_WASD_02) -已找到子记录日志—解释同delete

SQL> update wasd_01 set wasd_id=7,wasd_name='wasd7' where wasd_id=1;

update wasd_01 set wasd_id=7,wasd_name='wasd7' where wasd_id=1

ORA-02292:违反完整约束条件(SYS.FK_WASD_02) -已找到子记录日志所有相关字段一块修改也不行

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9719235/viewspace-571437/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9719235/viewspace-571437/