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;
上一篇: JavaScript 取一个月的最后一天