--完整性约束
--非空约束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';
--非空约束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';