Oracle约束管理

--约束（检查、校验）
--创建约束的三种方式：1.定义时加在字段后，2.定义表时写在语句最后，3.声明表后再追加
--五种约束：非空约束、唯一约束、检查约束、主键、外键
--1.非空约束：被非空约束修饰的字段不能为空not null（可简写为nn）,列级（只针对这一列生效）
--方式一：定义时加在字段后
create table EMPP
(
  empno    NUMBER(4) not null,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
)
--错误，empno不允许为空
insert into empp(ename) values ('chen');

--方式二：定义表时写在语句最后
--非空约束为列级约束，方式2只添加表级约束

--方式三：声明表后再追加
--非空约束为列级约束，方式3只添加表级约束
alter table empp add constraint nn_empp_ename not null;

--2.唯一约束,表中该列数据不允许重复(本次要求ename唯一)
--方式一：定义时加在字段后
drop table empp PURGE;
create table EMPP
(
  empno    NUMBER(4) not null,
  ename    VARCHAR2(10) unique,
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
)
insert into empp(empno,ename) values (1,'chen');
--错误，违反唯一约束条件
insert into empp(empno,ename) values (1,'chen');

--方式二：定义表时写在语句最后(empno和ename联合起来是唯一的)
create table EMPP
(
  empno    NUMBER(4) not null,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2),
  constraint uq_empp_ename unique(empno,ename)
);
insert into empp(empno,ename) values (1,'chen');
insert into empp(empno,ename) values (2,'chen');

--方式三：声明表后再追加(empno和ename联合起来是唯一的)
alter table empp add constraint uq_empp_ename unique(empno,ename);

--3.检查约束：指定列的值必须满足要求的条件（假设job只能取员工或经理，sal工资不能小于1000）
--注：如果检查约束要引用其他列，则不能使用列级约束，必须使用表级约束（方式2或3）
--方式一：定义时加在字段后
create table EMPP
(
  empno    NUMBER(4) not null,
  ename    VARCHAR2(10),
  job      VARCHAR2(9) check (job in ('员工','经理')) ,
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2) check(sal>1000),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);
--错误，sal不符合大于1000的条件
insert into empp (empno,ename,job,sal) values (1,'CHEN','经理',900);

--方式二：定义表时写在语句最后(工资+奖金不能小于1000)
create table EMPP
(
  empno    NUMBER(4) not null,
  ename    VARCHAR2(10),
  job      VARCHAR2(9) ,
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2) check(sal>1000),
  comm     NUMBER(7,2),
  deptno   NUMBER(2),
  constraint ck_empp_job_sal check (sal+comm>1000);
);
--方式三：声明表后再追加(工资+奖金不能小于1000)
alter table empp add constraint ck_empp_salandcomm check (sal+comm>1000);

--4.主键约束:指定表的主键，唯一标识（主键约束：非空且唯一）
--方式一：定义时加在字段后
create table EMPP
(
  empno    NUMBER(4) primary key,
  ename    VARCHAR2(10),
  job      VARCHAR2(9) ,
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);
--方式二：定义表时写在语句最后
create table EMPP
(
  empno    NUMBER(4),
  ename    VARCHAR2(10),
  job      VARCHAR2(9) ,
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2),
  constraint pk_empp_empnoandename primary key(empno,ename); 
);

--方式三：声明表后再追加
alter table empp add constraint pk_empp_empnoandename primary key(empno,ename);

--5.外键约束:引用另一个表的一列
--方式一：定义时加在字段后，无法添加外键约束

--方式2添加外键约束
--注：外键关联的字段必须为另一张表的唯一主键（不能是联合主键的其中一个）
create table EMPP
(
  empno    NUMBER(4),
  ename    VARCHAR2(10),
  deptno   NUMBER(4),
  constraint fk_empp_deptt_deptno foreign key(deptno) references deptt(deptno)
);
create table DEPTT
(
  deptno    NUMBER(4) primary key,
  dname     VARCHAR2(10)
);
--方式3添加外键约束
alter table empp add constraint fk_empp_deptt_deptno foreign key (deptno) references deptt(deptno) on delete cascade;

--新增这个员工的部门编号，在部门表中没有这个部门
--删除部门时，如果部门下有员工也不能删除这个部门，要先删净部门下员工(如果在创建序列规定了删除策略也可按策略删除)
insert into empp (empno,ename,deptno) values (12,'张三1',10);
insert into deptt (deptno,dname) values (10,'部门1');

delete from empp where deptno=10;
delete from deptt where deptno=10;