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_STUDENTselect u.* from user_constraints u where u.table_name = 'STUDENT1';--并列创建主键 SYS_C009786create 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/enablealter 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');--okselect * 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');--errorselect * 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_CUSTOMERSalter 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','[e-mail]');--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;--okupdate 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 cascadealter 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'--验证唯一性约束 emailinsert into t_user2(user_id, user_name, user_address, email) values(1,'张三','解放路1号','[e-mail]');--okinsert into t_user2(user_id, user_name, user_address, email) values(2,'李四','解放路2号','[e-mail]');--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_scorealter 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<=100insert into student4(stu_id, stu_name, score) values(1,'张三',100);--ok--创建员工表create table user_emp as select * from emp;--添加检查约束 chk_salaryalter 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);--erroinsert 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默认插入ACTinsert 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/1select trunc(sysdate,'mm') from dual;--2017/8/1select trunc(sysdate,'dd') from dual;--2017/8/20select trunc(sysdate,'hh24') from dual;--2017/8/20 22:00:00select trunc(sysdate,'mi') from dual;--2017/8/20 22:09:00select * from salesselect * 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;--唯一性约束