oracle表约束

1.主键约束

--【主键约束】
create table student(
       stu_id number not null primary key,
       stu_name varchar2(20),
       stu_birthday date,
       stu_address varchar2(50),
       stu_phone varchar2(20)
);
select s.* from student s;
--1.查看表约束 table_name = 'STUDENT'
select u.* from user_constraints u where u.table_name = 'STUDENT';
--2.查询主键建立在哪些列上 constraint_name = 'SYS_C009782'
select u.* from user_cons_columns u where u.constraint_name = 'SYS_C009782';
--1和2的结合
select u.* 
from user_cons_columns u 
where u.constraint_name = (select t.constraint_name from user_constraints t where t.table_name = 'STUDENT' and t.constraint_type = 'P');
--往student表中插入数据测试主键约束
insert into student(stu_id, stu_name, stu_birthday, stu_address, stu_phone) 
values(1,'张无忌',to_date('2017-06-06','yyyy-MM-dd hh24:mi::ss'),'人民路8号','18809179876');
--自定义约束名
create table student1(
       stu_id number not null constraint pk_student primary key,--显示命名主键约束名为 pk_student
       stu_name varchar2(20),
       stu_birthday date,
       stu_address varchar2(50),
       stu_phone varchar2(20)
);

--从约束表中查询出STUDENT1表的约束名字为PK_STUDENT
select u.* from user_constraints u where u.table_name = 'STUDENT1';
--并列创建主键 SYS_C009786
create table student2(
       stu_id number not null,
       stu_name varchar2(20),
       stu_birthday date,
       stu_address varchar2(50),
       stu_phone varchar2(20),
       primary key(stu_id)
);
select u.* from user_constraints u where u.table_name = 'STUDENT2';
--指定约束名
create table student3(
       stu_id number not null,
       stu_name varchar2(20),
       stu_birthday date,
       stu_address varchar2(50),
       stu_phone varchar2(20),
       constraint pk_student3 primary key(stu_id)
);
select u.* from user_constraints u where u.table_name = 'STUDENT3';
--指定约束名
create table student3(
       stu_id number not null,
       stu_name varchar2(20),
       stu_birthday date,
       stu_address varchar2(50),
       stu_phone varchar2(20),
       primary key(stu_id,stu_name)--创建多列约束
);
--增加单行主键
alter table student add [constraint pk_student] primary key(stu_id);
--1.直接删除主键
alter table student drop primary key;
--2.通过删除约束的方法删除主键
alter table student drop constraint pk_student;
--增加多行主键
alter table student modify primary key(stu_id,stu_name,stu_birthday,stu_address);
--禁用/启用主键disable/enable
alter table student disable primary key;
--禁用student表主键后
insert into student(stu_id, stu_name, stu_birthday, stu_address, stu_phone) 
values(1,'张无忌',to_date('2017-06-06','yyyy-MM-dd hh24:mi::ss'),'人民路8号','18809179876');--ok
select * from student;
--启用student表主键后,注意启用主键前要确保表中的主键符合主键约束否则会报错
alter table student enable primary key;
insert into student(stu_id, stu_name, stu_birthday, stu_address, stu_phone) 
values(1,'张无忌',to_date('2017-06-06','yyyy-MM-dd hh24:mi::ss'),'人民路8号','18809179876');--error
select * from student for update;
--重命名主键约束名
alter table student2 rename constraint SYS_C009786 to pk_student2;
select * from user_constraints where table_name = 'STUDENT2';

select * from user_constraints where table_name = 'STUDENT';
--主键的应用场景
--1.数据完整性约束
--2.是否对某列查询频繁
--3.是否存在外键引用

2.外键约束

--【外键约束：主键是单个表的约束，而外键则是多个表的关系，即他们的数据互相依存性】
--创建客户表
create table customers(
       customer_id number primary key,
       customer_name varchar2(50),
       customer_address varchar2(50),
       customer_phone varchar2(20),
       email varchar2(20)
);
--创建订单表
create table orders(
       order_id number primary key,
       customer_id number,goods_name varchar2(20),
       quantity number,
       unit varchar2(20)
);
--查询表约束
select u.* from user_constraints u where u.table_name = 'CUSTOMERS';
select u.* from user_constraints u where u.table_name = 'ORDERS';
--增加外键约束 FK_ORDERS_CUSTOMERS
alter table orders add constraint fk_orders_customers foreign key(customer_id) references customers(customer_id);

----------------Ⅰ.修改orders表时对customers表的影响--------------

--①向orders表插入数据验证外键的约束
insert into orders(order_id, customer_id, goods_name, quantity, unit) values(1,1,'FABRIC',20,'BMP');--error,父表中不存在customer_id=1的记录
--②向customer表中插入数据
insert into customers(customer_id, customer_name, customer_address, customer_phone, email) values(1,'李荣浩','解放路23号','17898988989','18382423@qq.com');--ok
--再执行①即可成功
insert into orders(order_id, customer_id, goods_name, quantity, unit) values(1,1,'FABRIC',20,'BMP');--ok
--修改order表数据验证外键约束
update orders set quantity=22 where order_id=1;--ok
update orders set customer_id=22 where order_id=1;--error,customers表中没有与之对应的数据
--综上，存在外键约束时，修改非外键列时可以成功；但是修改外键列时，如果父表不存在对应数据会报错；

----------------Ⅱ.修改customers表时对orders表的影响--------------
update customers set customer_id=22 where customer_id=1;--error,存在被引用的外键

--级联更新：deferrable initially deferred[immediate]
--创表时默认即时校验（immediate），要级联更新就要修改外键为延迟校验（deferred）
--级联更新中，延迟校验只是把校验变成了事务级，而即时校验可以说是语句级校验
--创建外键约束为延迟校验
alter table orders 
add constraint pk_orders_customers 
foreign key(customer_id) references customers(customer_id) deferrable initially deferred;
--验证级联更新
update customers set customer_id=3 where customer_id=1;
update orders set customer_id=3 where order_id=1;
commit;

--级联删除：on delete cascade
alter table orders 
add constraint pk_orders_customers 
foreign key(customer_id) references customers(customer_id) on delete cascade;
--验证级联删除
delete from customers where customer_id=3;

--重命名外键
alter table customers rename constraint pk_orders_customers to pk_orders_customers_update;
--禁用/启用外键
alter table orders modify constraint pk_orders_customers disable;
alter table orders modify constraint pk_orders_customers enable;
--删除外键
alter table orders drop constraint PK_ORDERS_CUSTOMERS;

--是否对已有数据进行校验 novalidate[validate]
alter table orders modify constraint pk_orders_customers enable validate;

select * from user_constraints where table_name='ORDERS';

select * from orders;
select * from customers;


--应用场景
--1.严格遵守父子关系的数据表应该使用外键
--2.将应用程序中的父子关系转移到外键约束
--3.不要过分使用外键 
--唯一性约束
--检查性约束
--默认值约束

3.唯一性约束

--【唯一性约束】
--主键约束是记录级唯一标识，而唯一性约束是列级唯一标识
create table t_user2(
       user_id number not null,
       user_name varchar2(20),
       user_address varchar2(50),
       email varchar2(20) unique,
       primary key(user_id)
);

select * from t_user2;

select * from user_constraints where table_name='T_USER2';
select * from user_cons_columns where constraint_name='SYS_C009805'
--验证唯一性约束 email
insert into t_user2(user_id, user_name, user_address, email) values(1,'张三','解放路1号','123@123.com');--ok
insert into t_user2(user_id, user_name, user_address, email) values(2,'李四','解放路2号','123@123.com');--error，违反email的唯一性约束
--增加唯一性约束
alter table t_user2 add constraint uq_user_name unique(user_name);
--重命名唯一性约束
alter table t_user2 rename constraint uq_user_name to uq_user_name_update;
--删除唯一性约束
alter table t_user2 drop constraint uq_user_name_update;
--禁用/启用唯一性约束enable[disable]
alter table t_user2 modify constraint uq_user_name enable;

--应用场景：作为主键的补充，适用于对业务逻辑要求唯一的场景

4.检查约束

--【检查约束】对列值进行约束;如果符合校验则返回true，反之返回false

--创建学生表
create table student4(
       stu_id number not null,
       stu_name varchar2(10),
       score number,
       primary key(stu_id)
);
--添加检查约束 chk_score
alter table student4 add constraint chk_score check(score between 0 and 100);
--1.验证检查约束 score(0,100)
insert into student4(stu_id, stu_name, score) values(1,'张三',101);--error，违反检查约束条件 0<=score<=100
insert into student4(stu_id, stu_name, score) values(1,'张三',100);--ok
--创建员工表
create table user_emp as select * from emp;
--添加检查约束 chk_salary
alter table user_emp 
add constraint chk_salary 
check(job in('CLERK','SALESMAN','MANAGER','ANALYST','PRESIDENT') 
and (job='CLERK' and sal<1500 or job='SALESMAN' and sal<2000 or job='MANAGER' and sal<3000 or job='ANALYST' and sal<3500 or job='PRESIDENT' and sal<10000));
--2.验证检查约束
insert into user_emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
values(6666,'KEVIN','CLERK',7902,to_date('2017-8-20','yyyy-MM-dd'),'1501',0,20);--erro

insert into user_emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
values(6666,'KEVIN','CLERK',7902,to_date('2017-8-20','yyyy-MM-dd'),'1499',0,20);--ok

--增加检查约束
alter table user_emp add constraint chk_deptno check(deptno in(10,20,30))
--重命名检查约束
alter table user_emp rename constraint chk_deptno to chk_deptno_update;
--删除检查约束
alter table user_emp drop constraint chk_deptno_update;
--禁用/启用检查约束
alter table user_emp modify constraint chk_deptno_update enable;

select * from user_emp;

select * from user_constraints where table_name='USER_EMP';
select * from user_cons_columns where constraint_name='CHK_SALARY' 

--应用场景：可以灵活的对列值进行约束

5.默认值约束

--【默认值约束】
--创建购物车订单表
create table purchase_order(
       purchase_order_id number,
       good_name varchar(30),
       quantity number,
       price number,
       status varchar2(3) default 'ACT',
       primary key(purchase_order_id)
);
--查看表中某列的类型
select * from user_tab_columns where table_name='PURCHASE_ORDER' and column_name='STATUS'
--验证默认值约束    status默认插入ACT
insert into purchase_order(purchase_order_id, good_name, quantity, price)
values(1,'华为mate9','6','18000');

select * from purchase_order;

select * from user_constraints where table_name='PURCHASE_ORDER';
select * from user_cons_columns where constraint_name='SYS_C009812';

--创建销售表 函数作为默认值约束
create table sales(
       id number not null,
       product_name varchar2(20),
       price number,
       quantity number,
       total number,
       sales_date date default sysdate,
       primary key(id)
);

--验证默认值约束
insert into sales(id, product_name, price, quantity, total) values(1,'SWEATER',40,2,20);
insert into sales(id, product_name, price, quantity, total) values(2,'BOOK',40,2,20);
insert into sales(id, product_name, price, quantity, total) values(3,'BAG',40,2,20);
insert into sales(id, product_name, price, quantity, total) values(4,'APPLE',40,2,20);

--增加默认值约束
alter table sales modify quantity number default 66;
--删除默认值约束
alter table sales modify quantity number default null;
--修改默认值约束
alter table sales modify quantity number default 666;

select trunc(sysdate,'yyyy') from dual;--2017/1/1
select trunc(sysdate,'mm') from dual;--2017/8/1
select trunc(sysdate,'dd') from dual;--2017/8/20
select trunc(sysdate,'hh24') from dual;--2017/8/20 22:00:00
select trunc(sysdate,'mi') from dual;--2017/8/20 22:09:00

select * from sales

select * from user_constraints where table_name='SALES';
select * from user_cons_columns where constraint_name='SYS_C009814';

6.约束综合练习

--创建教师表
create table teacher(
       teacher_id number,
       teacher_name varchar2(30)
);

--给teacher表添加约束
alter table teacher modify teacher_id number not null primary key;--主键约束
alter table teacher add constraint uq_teacher_name unique(teacher_name);--唯一性约束

select * from user_constraints where table_name='TEACHER';

--给teacher表添加数据
insert into teacher(teacher_id,teacher_name) values(1,'张老师');
insert into teacher(teacher_id,teacher_name) values(2,'李老师');
insert into teacher(teacher_id,teacher_name) values(3,'王老师');

select * from teacher;

--创建学生表
create table student5(
       stu_id number not null,
       teacher_id number,
       stu_name varchar(30),
       sex varchar2(2)
);

--给student5表添加约束
alter table student5 add primary key(stu_id);--主键约束
alter table student5 add constraint fk_student5_teacher foreign key(teacher_id) references teacher(teacher_id);--外键约束
alter table student5 add constraint uq_stu_name unique(stu_name);--唯一性约束
alter table student5 add constraint chk_sex check(sex in('男','女'));
alter table student5 modify sex varchar(2) default '男';

select * from user_constraints where table_name='STUDENT5';
alter table student5 drop constraint fk_student5_teacher;

--给student5表添加数据
insert into student5(stu_id, teacher_id, stu_name, sex) values(1,1,'张三','男');
insert into student5(stu_id, teacher_id, stu_name, sex) values(2,1,'李四','男');
insert into student5(stu_id, teacher_id, stu_name) values(3,1,'王五');

select * from student5;
select * from teacher;

--删除student5中的所有约束
alter table student5 drop primary key;--主键约束
alter table student5 drop constraint FK_STUDENT5_TEACHER;--外键约束
alter table student5 drop constraint UQ_STU_NAME;--唯一性约束
alter table student5 drop constraint CHK_SEX;--检查约束
alter table student5 modify sex varchar(2) default null;--默认值约束

--删除teacher中的所有约束
alter table teacher drop primary key;--主键约束
alter table teacher drop constraint UQ_TEACHER_NAME;--唯一性约束