Oracle完整约束

来源：互联网发布：守望先锋优化补丁下载编辑：程序博客网时间：2024/05/17 07:16

--完整性约束

--非空约束NK

drop table member purge;

create table member(
mid number,
name varchar2(200) not null
);

insert into member(mid,name) values(1,'李兴华');

insert into member(mid) values(3);

--唯一约束UK

drop table member purge;
create table member(
mid number,
name varchar2(200) not null,
email varchar2(50) unique
);

insert into member(mid,name,email) values(1,'陈飞鹏','516067656@qq.com');
insert into member(mid,name,email) values(2,'张三','516067656@qq.com');

drop table member purge;
create table member(
mid number,
name varchar2(200) not null,
email varchar2(50),
constraint uk_email unique(email)
);

insert into member(mid,name,email) values(1,'陈飞鹏','516067656@qq.com');
insert into member(mid,name,email) values(2,'张三','516067656@qq.com');
insert into member(mid,name,email) values(3,'陈飞鹏',null);
insert into member(mid,name,email) values(4,'张三',null);

select * from member;

--主键约束PK

drop table member purge;
create table member(
mid number primary key,
name varchar2(200) not null,
email varchar2(50),
constraint uk_email unique(email)
);

insert into member(mid,name,email)values(1,'陈飞鹏','516067656@qq.com');
insert into member(mid,name,email)values(1,'李翔','1764523287@qq.com');

select * from member;

drop table member purge;
create table member(
mid number,
name varchar2(200) not null,
email varchar2(50),
constraint pk_mid primary key(mid),
constraint uk_email unique(email)
);

insert into member(mid,name,email)values(1,'陈飞鹏','516067656@qq.com');
insert into member(mid,name,email)values(1,'李翔','1764523287@qq.com');

drop table member purge;
create table member(
mid number,
name varchar2(200) not null,
email varchar2(50),
constraint pk_mid_name primary key(mid,name),
constraint uk_email unique(email)
);

insert into member(mid,name,email)values(1,'陈飞鹏','516067656@qq.com');
insert into member(mid,name,email)values(1,'李翔','1764523287@qq.com');
insert into member(mid,name,email)values(1,'陈飞鹏','2448232544@qq.com');

select * from member;

--检查约束

drop table member purge;
create table member(
mid number,
name varchar2(200) not null,
email varchar2(50),
age number check(age between 0 and 200),
sex varchar2(10),
constraint pk_mid primary key(mid),
constraint uk_email unique(email),
constraint ck_sex check(sex in('男','女'))
);

insert into member(mid,name,email,age,sex)values(1,'陈飞鹏','516067656@qq.com',22,'男');
insert into member(mid,name,email,age,sex)values(2,'陈飞鹏','516067656@qq.com',900,'男');
insert into member(mid,name,email,age,sex)values(3,'陈飞鹏','516067656@qq.com',22,'无');

select * from member;

--主-外键约束FK

drop table member purge;
drop table advice purge;
create table member(
mid number,
name varchar2(200) not null,
constraint pk_mid primary key(mid)
);
create table advice(
adid number,
content clob not null,
mid number,
constraint pk_adid primary key(adid)
);

insert into member(mid,name)values(1,'陈飞鹏');
insert into member(mid,name)values(2,'李翔');

insert into advice(adid,content,mid)values(1,'应该提倡内部沟通机制，设置总裁邮箱',1);
insert into advice(adid,content,mid)values(2,'为了使公司内部良性发展，所有的部门领导应该重新应聘上岗',1);
insert into advice(adid,content,mid)values(3,'要多开展员工培训活动，让员工更加有归属感',1);
insert into advice(adid,content,mid)values(4,'应该开展多元化业务，更加满足市场需求',2);
insert into advice(adid,content,mid)values(5,'大力发展技术部门，为本公司设计自己的ERP系统，适应电子化信息发展要求',2);

commit;

select * from member;
select * from advice;

select m.mid,m.name,count(a.mid)
from member m,advice a
where m.mid=a.mid
group by m.mid,m.name;

insert into advice(adid,content,mid)values(6,'岗位职责透明化',99);

select * from advice;

drop table member purge;
drop table advice purge;
create table member(
mid number,
name varchar2(200) not null,
constraint pk_mid primary key(mid)
);
create table advice(
adid number,
content clob not null,
mid number,
constraint pk_adid primary key(adid),
constraint fk_mid foreign key(mid) references member(mid)
);

insert into member(mid,name)values(1,'陈飞鹏');
insert into member(mid,name)values(2,'李翔');

insert into advice(adid,content,mid)values(1,'应该提倡内部沟通机制，设置总裁邮箱',1);
insert into advice(adid,content,mid)values(2,'为了使公司内部良性发展，所有的部门领导应该重新应聘上岗',1);
insert into advice(adid,content,mid)values(3,'要多开展员工培训活动，让员工更加有归属感',1);
insert into advice(adid,content,mid)values(4,'应该开展多元化业务，更加满足市场需求',2);
insert into advice(adid,content,mid)values(5,'大力发展技术部门，为本公司设计自己的ERP系统，适应电子化信息发展要求',2);
insert into advice(adid,content,mid)values(6,'岗位职责透明化',99);

commit;

select * from member;
select * from advice;

delete from member where mid=1;

drop table member purge;
drop table advice purge;
create table member(
mid number,
name varchar2(200) not null,
constraint pk_mid primary key(mid)
);
create table advice(
adid number,
content clob not null,
mid number,
constraint pk_adid primary key(adid),
constraint fk_mid foreign key(mid) references member(mid) on delete cascade
);

insert into member(mid,name)values(1,'陈飞鹏');
insert into member(mid,name)values(2,'李翔');

insert into advice(adid,content,mid)values(1,'应该提倡内部沟通机制，设置总裁邮箱',1);
insert into advice(adid,content,mid)values(3,'要多开展员工培训活动，让员工更加有归属感',2);
commit;

select * from member;
select * from advice;

delete from member where mid=1;

select * from member;
select * from advice;

drop table member purge;
drop table advice purge;
create table member(
mid number,
name varchar2(200) not null,
constraint pk_mid primary key(mid)
);
create table advice(
adid number,
content clob not null,
mid number,
constraint pk_adid primary key(adid),
constraint fk_mid foreign key(mid) references member(mid) on delete set null
);

insert into member(mid,name)values(1,'陈飞鹏');
insert into member(mid,name)values(2,'李翔');

insert into advice(adid,content,mid)values(1,'应该提倡内部沟通机制，设置总裁邮箱',1);
insert into advice(adid,content,mid)values(3,'要多开展员工培训活动，让员工更加有归属感',2);
commit;

select * from member;
select * from advice;

delete from member where mid=1;

drop table member cascade constraint;

--查看约束

drop table advice purge;
drop table member purge;
purge recyclebin;

create table member(
mid number primary key,
name varchar2(200) not null
);
desc member;
insert into member(mid,name)values(1,'张三');
insert into member(mid,name)values(2,'李四');
--违反唯一约束条件
insert into member(mid,name)values(1,'王五');

select * from user_cons_columns;

select constraint_name,constraint_type,table_name from user_constraints;
select constraint_name,constraint_type,table_name from user_constraints where table_name='EMP';

0 0