【Oracle学习】之外键约束总结_oracle外键约束怎么写

主表和子表的概念： 如果表中定义了外键约束，那么该表通常被称为子表；如果表中包含引用键，那么该表被称为父表。

一、设置主键

1.在创建表时候设置外键约束（列级）
create table table1(column_name datatype REFERENCES table2(column_name,....));

其中table1 的表为从表，table2的表为主表

注意：在设置外键约束的时候，主表的字段必须是主键。

          主从表中相应的字段必须是相同数据类型，字段名称可以不一致。

          从表中外键字段的值必须来自主表中的相应字段的值，或者为NULL

 

2.在创建表时设置外键约束（表级）
CONSTRAINT  constraint_name  FOREIGN KEY(column_name)References

  table_name(column_name)[on delete cascade]

 

3.在修改表时添加外键约束
alter table table_name add constraint constraint_name FOREIGN KEY(column_name)REFERENCES

table_name(column_name)

 

4.禁用外键约束
alter table table_name disable(禁用) | enable （启用）CONSTRAINT  constraint_name

5.删除外键
alter table table_name drop  constraint   constraint_name;

 

二、查看外键

ORACLE数据库中的外键约束名都在表user_constraints中可以查到。其中constraint_type='R'表示是外键约束

例子：

//查看当前用户绑定schema的所有外键
select * from user_constraints where constraint_type='R';
//查看特定表的外键
select constraint_type, table_name, status from user_constraints where table_name = 'TEST_STUDENT';

 

三、主表删除外键关联数据

注：以下几种删除都是在外键激活状态下进行的分类讨论

1、默认删除—-即什么都不加 或 使用 no action关键字

简介：如果在定义外键约束时使用no action关键字，那么当父表中被引用列的数据被删除时，将违反外键约束，改操作也将被禁止执行，这也是外键约束的默认引用类型。

//查看test_student 有哪些约束
select constraint_type, table_name from user_constraints where table_name = 'TEST_STUDENT';
 
//添加约束（此处使用默认值即no action）
alter table test_student add constraint FK_TAB_STUDNET_TAB_CLASS foreign key (s_fk_id) references test_class (c_id);
 
select * from test_class;
select * from test_student; 
delete from test_class where c_id = 1;

2、级联删除—-使用关键字 cascade

简介：如果在定义外键约束时使用cascade关键字，那么当父表中被引用列的数据被删除时，子表中对应的数据也将被删除。

//删除
alter table test_student drop constraint FK_TAB_STUDNET_TAB_CLASS;
//添加
alter table test_student add constraint FK_TAB_STUDNET_TAB_CLASS foreign key (s_fk_id) references test_class (c_id) on delete cascade;
 
select * from test_class;
select * from test_student; 
delete from test_class where c_id = 1;

3、置空删除—-使用关键字set null

简介：如果在定义外键约束时使用set null关键字，那么当主表中被引用列的数据被删除时，子表中对应的数据被设置为null。要使这个关键字起作用，子表中的对应列必须支持NULL值。

//删除
alter table test_student drop constraint FK_TAB_STUDNET_TAB_CLASS;
//添加
alter table test_student add constraint FK_TAB_STUDNET_TAB_CLASS foreign key (s_fk_id) references test_class (c_id) on delete set null;
 
select * from test_class;
select * from test_student; 
delete from test_class where c_id = 1;

四、外键约束激活与失活

1、禁止状态（DISABLE）：当约束处于禁止状态时，即使对表的操作与约束规则相冲突，操作也会被执行。

//使失效
alter table TABLE_NAME disable constraint FK_NAME;
//查看状态
select constraint_type, table_name, status from user_constraints where table_name = 'TABLE_NAME';

2、激活状态（ENABLE）：当约束处于激活状态时，如果对表的操作与约束规则相冲突，则操作会被取消。

//使有效
alter table table_name enable constraint FK_NAME;
//查看状态
select constraint_type, table_name, status from user_constraints where table_name = 'TABLE_NAME';

Tips：用SQL查出数据库中所有外键的约束名并使之激活/失活：

//查出所有外键并写下激活所有外键sql语句
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R';
//查出所有外键并写下失活所有外键sql语句
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R';