Oracle的表操作——外键约束

1、外键约束：外键（foreign key）是用来建立和加强两个表数据之间的连接的一列或多列。外键约束是唯一涉及两个表关系的约束。

注：先创建的表叫主表或附表，后创建的且和主表有关系的表叫从表或子表

2、外键约束的注意事项：

（1）、设置外键约束时主表的字段必须是主键列（或唯一列）

（2）、主从表中相应字段必须是同一数据类型

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

3、创建表时设置外键约束的方法

（1）、列级约束：create table 从表 ( column_name datatype references 主表 ( column_name) [ on delete cascade ],...);

例：

主表：

create table department(

                                             depid varchar2(10) primary key,

                                             depname varchar2(30)

                                            );

子表：

create table student(

                                      sid number(8,0),

                                       name varchar2(20),

                                       sex char(2),

                                       birthday date,

                                       address varchar2(50),

                                        depid varchar2(10) references department(depid)

                                    );

注：子表中的depid可以随意取名

（2）、表级约束：constraint constraint_name foreign key ( column_name ) references 主表 ( column_name) [ on delete cascade]

例：

主表：

create table department(

                                             depid varchar2(10) primary key,

                                             depname varchar2(30)

                                            );

子表：

create table student(

                                      sid number(8,0),

                                       name varchar2(20),

                                       sex char(2),

                                       birthday date,

                                       address varchar2(50),

                                       constraint fk_depid foreign key(depid) references department(depid) on delete cascade

                                    );

4、修改表时添加外键约束的方法：alter table  student add constraint fk_depid foreign key(depid) references department(depid) on delete cascade;

5、删除外键约束的方法：

（1）、禁用约束：alter table  table_name disable constraint column_name;

（2）、彻底删除：alter table table_name drop constraint column_name;