对wasd_02进行dml操作
SQL> delete from wasd_02 where wasd_id=1;
1 row deleted
SQL> rollback;
Rollback complete---说明删除外键约束表wasd_02的数据是可以的
SQL> insert into wasd_02 values (5,'wasd5',sysdate);
insert into wasd_02 values (5,'wasd5',sysdate)
ORA-02291:违反完整约束条件(SYS.FK_WASD_02) -未找到父项关键字—说明在外键表wasd_02插入数据首先要得到wasd_01的认可。
SQL> update wasd_02 set wasd_id=7 where wasd_id=1;
update wasd_02 set wasd_id=7 where wasd_id=1
ORA-02291:违反完整约束条件(SYS.FK_WASD_02) -未找到父项关键字—说明在外键表wasd_02更新主键字段数据首先要得到wasd_01的认可。
SQL> update wasd_02 set wasd_id=7,wasd_name='wasd7' where wasd_id=1;
update wasd_02 set wasd_id=7,wasd_name='wasd7' where wasd_id=1
ORA-02291:违反完整约束条件(SYS.FK_WASD_02) -未找到父项关键字所有相关字段一块修改也不行
测试外键可否建立与非主键字段上
SQL> alter table wasd_02 add(constraint fk_wasd_02_11 foreign key (wasd_id)
2 references wasd_01(wasd_id)) ;
alter table wasd_02 add(constraint fk_wasd_02_11 foreign key (wasd_id)
references wasd_01(wasd_id))
ORA-02270:此列列表的唯一或主键不匹配
测试一条主键数据对多条外键数据
SQL> insert into wasd_02 values (1,'wasd1',sysdate-10);
1 row inserted
SQL> COMMIT;
Commit complete
SQL> update wasd_02 set wasd_id=10
2 where wasd_id = 1
3 and wasd_date = to_date('2009-3-1 23:14:09', 'yyyy-mm-dd hh24:mi:ss')
4 ;
update wasd_02 set wasd_id=10
where wasd_id = 1
and wasd_date = to_date('2009-3-1 23:14:09', 'yyyy-mm-dd hh24:mi:ss')
ORA-02291:违反完整约束条件(SYS.FK_WASD_02) -未找到父项关键字
总结:
对于这个主键、外键约束,强调外键表wasd_02:wasd_id,wasd_name有的值,主键表wasd_01必须也要有,这个是外键有效的前提,也是外键约束的存在意义。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9719235/viewspace-571438/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9719235/viewspace-571438/