约束 1、主键约束 : primary key select * from student08; create table student08 ( id number primary key, /*创建主键约束*/ name varchar2(20), sex varchar2(20), age number, address varchar2(20) ) tablespace test insert into student08(id,name,sex,age,address) values(1,‘yuanling‘,‘nv‘,27,‘zhejiang‘); insert into student08(id,name,sex,age,address) values(1,‘yuan‘,‘nv‘,26,‘jiang‘); /*违反了主键唯一性约束*/ drop table student08; ---------------------------------------------------------------------------------------------------------------------- 显性的为主键取别名 select * from student08; create table student08 ( id number constraint pk_student08 primary key, /*创建主键约束*/ name varchar2(20), sex varchar2(20), age number, address varchar2(20) ) tablespace test insert into student08(id,name,sex,age,address) values(1,‘yuanling‘,‘nv‘,27,‘zhejiang‘); insert into student08(id,name,sex,age,address) values(1,‘yuan‘,‘nv‘,26,‘jiang‘); /*违反了主键唯一性约束*/ drop table student08; ----------------------------------------------------------------------------------------------------------------------- select * from student08; create table student08 ( id number, /*创建主键约束*/ name varchar2(20), sex varchar2(20), age number, address varchar2(20), constraint pk_student08 primary key( id ) ) tablespace test insert into student08(id,name,sex,age,address) values(1,‘yuanling‘,‘nv‘,27,‘zhejiang‘); insert into student08(id,name,sex,age,address) values(1,‘yuan‘,‘nv‘,26,‘jiang‘); drop table student08; ---------------------------------------------------------------------------------------------------------------------------------- select * from student08; create table student08 ( id number, /*创建主键约束*/ name varchar2(20), sex varchar2(20), age number, address varchar2(20), constraint pk_student08 primary key( id,name ) ) tablespace test insert into student08(id,name,sex,age,address) values(1,‘yuanling‘,‘nv‘,27,‘zhejiang‘); insert into student08(id,name,sex,age,address) values(1,‘yuanling01‘,‘nv‘,27,‘zhejiang‘); insert into student08(id,name,sex,age,address) values(1,‘yuanling‘,‘nv‘,27,‘zhejiang‘); insert into student08(id,name,sex,age,address) values(1,‘yuanling01‘,‘nv‘,27,‘zhejiang‘); drop table student08; ---------------------------------------------------------------------------------------------------- 如果一个表创建时，并没有指定主键，那么在表创建之后，照样可以添加主键 select * from student08; create table student08 ( id number, name varchar2(20), sex varchar2(20), age number, address varchar2(20) ) tablespace test 修改列属性 --------- alter table student08 modify( id number primary key); /*modify选项实际为修改列属性，并在修改列属性的同时，将该列指定为主键列*/ 为表添加多列（而不是修改列属性） alter table student08 add constraint pk_student08 primary key ( id,name ); insert into student08(id,name,sex,age,address) values(1,‘yuanling‘,‘nv‘,27,‘zhejiang‘); insert into student08(id,name,sex,age,address) values(1,‘yuanling01‘,‘nv‘,27,‘zhejiang‘); insert into student08(id,name,sex,age,address) values(1,‘yuanling‘,‘nv‘,27,‘zhejiang‘); drop table student08; 删除主键：alter table student08 drop primary key; 利用删除约束的语法删除主键，因为表的约束可能有好多了，所以指定名称 alter table student08 drop constraint pk_student08; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ select * from student08; create table student08 ( id number, name varchar2(20), sex varchar2(20), age number, address varchar2(20) ) tablespace test alter table student08 add constraint pk_student08 primary key ( id,name ); alter table student08 drop constraint pk_student08; ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 禁用/启用 修改主键名 主键约束 select * from student08; create table student08 ( id number constraint pk_student08 primary key, /*创建主键约束*/ name varchar2(20), sex varchar2(20), age number, address varchar2(20) ) tablespace test insert into student08(id,name,sex,age,address) values(1,‘yuanling‘,‘nv‘,27,‘zhejiang‘); alter table student08 disable primary key; alter table student08 enable primary key; insert into student08(id,name,sex,age,address) values(1,‘yuan‘,‘nv‘,26,‘jiang‘); /*违反了主键唯一性约束*/ drop table student08; alter table student08 rename constraint pk_student08 to zj; alter table student08 rename constraint zj to pk_student08;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2、外键约束
======================================================================================================================= 3、唯一性约束 select * from student08; create table student08 ( id number primary key, /*创建主键约束*/ name varchar2(20) unique, /*创建唯一性约束*/ sex varchar2(20), age number, address varchar2(20) ) tablespace test insert into student08(id,name,sex,age,address) values(1,‘yuanling‘,‘nv‘,27,‘zhejiang‘); insert into student08(id,name,sex,age,address) values(2,‘yuanling‘,‘nan‘,20,‘jiangsu‘); /*违反了name设置的唯一性*/ insert into student08(id,name,sex,age,address) values(2,‘yuanling01‘,‘nan‘,20,‘jiangsu‘); insert into student08(id,name,sex,age,address) values(3,‘‘,‘nan‘,25,‘jiangsu‘); insert into student08(id,name,sex,age,address) values(4,‘‘,‘nan‘,28,‘jiangsu09‘); ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 已经存在的表中添加唯一性约束 注意：与主键不同，一个唯一性约束只能建立在一个列上，因此，小括号内的列名只能有一个，不能是多个列名的列表； 要想在多个列上建立唯一性约束，只能针对每一个列分别创建； select * from student08; create table student08 ( id number primary key, /*创建主键约束*/ name varchar2(20), /*创建唯一性约束*/ sex varchar2(20), age number, address varchar2(20) ) tablespace test ---------------------------------------------------------------------------------------------- 新建一个唯一性约束： alter table student08 add constraint wyxys unique ( name ); 重命名唯一性约束： alter table student08 rename constraint wyxys to wyx_ys; alter table student08 rename constraint wyx_ys to wyxys; 禁用唯一性约束： alter table student08 disable constraint wyxys; 或者 alter table student08 modify constraint wyxys enable; 启用唯一性约束： alter table student08 enable constraint wyxys; 删除唯一性约束： alter table student08 drop constraint wyxys; --------------------------------------------------------------------------- select * from student08; insert into student08(id,name,sex,age,address) values(1,‘yuanling‘,‘nv‘,27,‘zhejiang‘); insert into student08(id,name,sex,age,address) values(2,‘yuanling‘,‘nan‘,20,‘jiangsu‘); /*违反了name设置的唯一性*/ insert into student08(id,name,sex,age,address) values(2,‘yuanling01‘,‘nan‘,20,‘jiangsu‘); insert into student08(id,name,sex,age,address) values(3,‘‘,‘nan‘,25,‘jiangsu‘); insert into student08(id,name,sex,age,address) values(4,‘‘,‘nan‘,28,‘jiangsu09‘); =======================================================================================================================================
4、检查约束 select * from student08; drop table student08; create table student08 ( id number primary key, /*创建主键约束*/ name varchar2(20), /*创建唯一性约束*/ sex varchar2(20), age number, address varchar2(20), constraint chk_age check ( age between 20and 50) ) tablespace test insert into student08(id,name,sex,age,address) values(1,‘yuanling‘,‘nv‘,27,‘zhejiang‘); insert into student08(id,name,sex,age,address) values(2,‘yuanling‘,‘nan‘,50,‘jiangsu‘); insert into student08(id,name,sex,age,address) values(3,‘yuanling‘,‘nan‘,19,‘jiangsu‘); insert into student08(id,name,sex,age,address) values(4,‘yuanling‘,‘nan‘,51,‘jiangsu‘); ------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------- create table employees ( id number primary key, name varchar2(50), grade varchar2(50), salary number, constraint chk_salary check ( grade in (‘manager‘,‘leader‘,‘staff‘) and ( grade = ‘manager‘ and salary <= 8000 or grade = ‘leader‘ and salary <= 5000 or grade = ‘staff‘ and salary <= 4000 )) ) tablespace test select * from employees; insert into employees( id,name,grade,salary) values (1,‘shen‘,‘manager‘,7800); insert into employees( id,name,grade,salary) values (2,‘zhou‘,‘leader‘,4800); insert into employees( id,name,grade,salary) values (3,‘yuan‘,‘staff‘,3200); 插入非法数据： insert into employees( id,name,grade,salary) values (4,‘ling‘,‘staff‘,5000); ---------------------------- 重新命名约束： alter table employees rename constraint chk_salary to cksy; alter table employees rename constraint cksy to chk_salary; 禁用约束： alter table employees disable constraint chk_salary; 或者 alter table employees modify constraint chk_salary disable; 启用约束： alter table employees enable constraint chk_salary; 或者 alter table employees modify constraint chk_salary enable; 删除约束： alter table employees drop constraint chk_salary; 重新添加约束： alter table employees add constraint chk_salary check ( grade in (‘manager‘,‘leader‘,‘staff‘) and ( grade = ‘manager‘ and salary <= 8000 or grade = ‘leader‘ and salary <= 5000 or grade = ‘staff‘ and salary <= 4000 )); ===========================================================================================================================================================================================================================================
5、默认值约束 select * from student08; drop table student08; create table student08 ( id number, /*创建主键约束*/ name varchar2(20), /*创建唯一性约束*/ sex varchar2(20), age number default 22, address varchar2(20) default ‘abc‘ ) tablespace test insert into student08(id,name,sex,age,address) values(1,‘yuanling‘,‘nv‘,20,‘jiangsu‘); insert into student08(id,name,sex) values(2,‘shen‘,‘nan‘); --------------------------------------------------------------------------------------------------------- 使用函数作为默认值 create table sx ( id number, name varchar2(50), xssj date default sysdate ) tablespace test select * from sx; insert into sx (id,name) values (1,‘sww‘); --------------------------------------------------------------- create table sx02 ( id number, name varchar2(50), xssj date ) tablespace test select * from sx02; 添加默认值约束： alter table sx02 modify xssj default sysdate; insert into sx02 (id,name) values (1,‘sww‘); --------------------------------------------------- alter table sx02 modify name default ‘abc‘; insert into sx02 (id) values (2); 修改默认值： alter table sx02 modify name default ‘2wsdc‘; insert into sx02 (id) values (3); 删除默认值： alter table sx02 modify name varchar2(50) default null; insert into sx02 (id) values (8);
6、非空约束( not null ) create table sx03 ( id number, name varchar2(50), sex varchar2(10) not null ) tablespace test select * from sx03; insert into sx03 (id,name) values (1,‘sww‘); insert into sx03 (id,name,sex) values (1,‘sww‘,‘nv‘); 删除非空约束后，可以向表中插入数据（只需要把表的指定列的属性修改为null即可） alter table sx03 modify ( sex null); insert into sx03 (id,name) values (2,‘123ew2‘); ---------------------------------------------------------------------- 为已经存在的表，增加非空约束 create table sx04 ( id number, name varchar2(50), sex varchar2(10) ) tablespace test select * from sx04; insert into sx04 (id,name,sex) values (1,‘sww‘,‘nv‘); alter table sx04 modify ( sex not null); insert into sx04 (id,name,sex) values (2,‘edcx‘,‘nan‘); insert into sx04 (id,name) values (1,‘sww‘);
原文：https://www.cnblogs.com/xiaobaibailongma/p/12257664.html