oracle外键约束实现教程

1、在创建表时设置外键约束

涉及两个表中字段关系的约束

创建主表

create table typeinfo

(typeid varchar2(10) primary key,

typename varchar2(20) );

创建从表

create table userinfo_f

(id varchar2(10) primary key,

username varchar2(20),

typeid_new varchar2(10) references typeinfo(typeid) );

insert into typeinfo values(1,1);

insert into typeinfo_f(id,typeid_new) values(1,1);

insert into typeinfo_f(id,typeid_new) values(2,null);

“““““““““““““““““““““““““““““““`

create table userinfo_f2

(id varchar2(10) primary key,

username varchar2(20),

typeid_new varchar2(10),

constraint fk_typeid_new foreign key(typeid_new) references typeinfo(typeid) );

通过级联删除来创建外键约束

create table userinfo_f3

(id varchar2(10) primary key,

username varchar2(20),

typeid_new varchar2(10),

constraint fk_typeid_new1 foreign key(typeid_new) references typeinfo(typeid) on delete cascade );

2、在修改表时添加外键约束

create table userinfo_f4

(id varchar2(10) primary key,

username varchar2(20),

typeid_new varchar2(10) );

alter table userinfo_f4

add constraint fk_typeid_alter foreign key(typeid_new) references typeinfo(typeid);

3、删除外键约束

查看约束的名字，类型，状态

select constraint_name,constraint_type,status from user_constraints where table_name=’userinfo_4’;

(1) 禁用(启用)外键约束(disable/enable)

alter table userinf0_4

disable constraint fk_typeid_alter;

select constraint_name,constraint_type,status from user_constraints where table_name=’userinfo_4’;

(2) 彻底删除外键约束

alter table userinf0_4

drop constraint fk_typeid_alter;