oracle外键约束学习

一、准备知识约束放置在表中，有以下五种约束:       NOT NULL          非空约束C     指定的列不允许为空值       UNIQUE             唯一约束U     指定的列中没有重复值，或该表中每一个值或者每一组值都将是唯一的       PRIMARY KEY       主键约束P     唯一的标识出表的每一行,且不允许空值值,一个表只能有一个主键约束       FOREIGN KEY       外键约束R     一个表中的列引用了其它表中的列，使得存在依赖关系，可以指向引用自身的列       CHECK              条件约束C     指定该列是否满足某个条件约束命名规则       如果不指定约束名Oracle server 自动按照SYS_Cn 的格式指定约束名，也可手动指定，       推荐的约束命名是：约束类型_表名_列名。       NN：NOT NULL          非空约束，比如nn_emp_sal       UK：UNIQUE KEY         唯一约束       PK：PRIMARY KEY       主键约束       FK：FOREIGN KEY       外键约束       CK：CHECK             条件约束外键约束是用来维护从表和主表的引用完整性的，所以外键约束要涉及两个表。           FOREIGN KEY: 在表级指定子表中的列           REFERENCES: 标示在父表中的列           ON DELETE CASCADE: 当父表中的列被删除时，子表中相对应的列也被删除           ON DELETE SET NULL: 子表中相应的列置空二、外键创建测试

foreign_main为主表

foreign_sub为从表

object_id做为foreign_sub的外键，参考主表foreign_main的object_id值

SQL> create table foreign_main as select object_id from all_objects; Table created. SQL> select count(*) from foreign_main; COUNT(*) ---------- 49571 SQL> create table foreign_sub as select object_id,object_name from all_objects; Table created.

建议使用主表的主键做外键，即使不是主表的主键也应该是唯一约束的字段做为外键

SQL> alter table foreign_main add constraint pk_fsid primary key(object_id); Table altered. SQL> delete from foreign_sub where object_name = 'FOREIGN_MAIN'; 1 row deleted. SQL> commit; Commit complete. SQL> alter table foreign_sub add constraint fr_fssid foreign key(object_id) references foreign_main(object_id); Table altered.

从表插入一条主表object_id中不存在的记录测试

SQL> insert into foreign_sub values(1,'ts'); insert into foreign_sub values(1,'ts') * ERROR at line 1: ORA-02291: integrity constraint (TEST.FR_FSSID) violated - parent key not found

提示主表数据不存在，从表不能创建主表不存在的object_id以保证完整性三、级联删除测试

SQL> alter table foreign_sub drop constraint fk_fs_oid; Table altered. SQL> alter table foreign_sub add constraint fk_fs_oid foreign key(object_id) references foreign_main(object_id) on delete cascade; Table altered.

cascade下仍然不能单独更新主表外键字段

SQL> update foreign_main set object_id=52012 where object_id=52010; update foreign_main set object_id=52012 where object_id=52010 * ERROR at line 1: ORA-02292: integrity constraint (TEST.FK_FS_OID) violated - child record found

cascade模式下可以通过主表删除外键字段数据关联删除从表数据

SQL> select * from foreign_sub where object_id=52010; OBJECT_ID OBJECT_NAME ---------- ------------------------------ 52010 IDX_BJNAME SQL> delete from foreign_main where object_id=52010; 1 row deleted. SQL> commit; Commit complete. SQL> select * from foreign_sub where object_id=52010; no rows selected

外键相关常用操作及参考文档建立外键alter table 表名 add constraint 外键名 foreign key(从表外键字段) references foreign_main(主表外键字段);drop表外键alter table 表名 drop constraint 外键名;通过外键找表select * from user_constraints where constraint_type='R' and constraint_name=upper('外键名');通过表找外键select * from user_constraints where constraint_type='R' and table_name=upper('表名');查找表的外键（包括名称，引用表的表名和对应的键名，下面是分成多步查询）：select * from user_constraints c where c.constraint_type = 'R' and c.table_name = 要查询的表查询引用表的键的列名：select * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名外键约束临时disabledalter table 表名 disable constraint 外键名;在SQL92标准中定义了几种外键改变后，如何处理子表记录的动作，其中包括：限制Restrict：这种方式不允许对被参考的记录的键值执行更新或删除的操作；置为空Set to null：当参考的数据被更新或者删除，那么所有参考它的外键值被置为空；置为默认值Set to default：当参考的数据被更新或者删除，那么所有参考它的外键值被置为一个默认值；级联Cascade：当参考的数据被更新，则参考它的值同样被更新，当参考的数据被删除，则参考它的子表记录也被删除；不做操作No action：这种方式不允许更新或删除被参考的数据。和限制方式的区别在于，这种方式的检查发生在语句执行之后。Oracle默认才会的方式就是这种方式。http://www.cnblogs.com/lanshh/articles/724458.htmlhttp://dbajun.iteye.com/blog/1822992http://space.itpub.net/10710960/viewspace-610982http://www.cnblogs.com/tracy/archive/2011/06/09/2076527.htmlby cycsa

转载于:https://www.cnblogs.com/cycsa/archive/2013/05/16/3082074.html