应用约束

一、约束先容

1.约束分类

?? no null

??? unique----列值不能频频，可为null,主动 成立索引

??? primary key------列值不能频频，不可为null，主动 成立索引

?? foreign key-------列值在主表中存在或为null

?? check

2.约束状况

?? enable validate(默认)-------新旧数据必需同时满意约束正直

?? enable novalidate----------已存在数据可以不满意约束正直，但新数据必需满意（也许存在频频值）

?? disable validate------------不允许推行 DML操纵（也许存在频频值）

?? disable novalidate----------数据可以不满意约束正直（也许存在频频值）（也许存在频频值）

二、掩护约束

1.建表时界说约束

A:列级界说

?? create table department(

?? dept_id number(2) constraint pk_department primary key,

?? name varchar2(14),loc varchar2(30));

B:表级界说

??? create table employee(

?? emp_id number(4),name varchar2(15),job varchar2(10),

??? manager_id number(4),hire_date date,salary number(7,2),

?? commission number(7,2),dept_id number(2),

?? constraint pk_employee primary key(emp_id) using index tablespace user02,

?? constarint fk_department foreign key(dept_id) references department(dept_id)

?? );

2.建表后增进约束

A:增进not null 约束

?? alter table department modify name not null

B:增进unique约束

?? alter table department add constraint u_deptname unique(name);

?? alter table department add constraint u_deptname unique(name) using index

?? (create index ind_name on department(name))------指定索引成立语句

C:增进check约束

?? alter table employee add constraint check_job check

?? (job in('MANAGER','ANALYST','SALESMAN','CLERK'));

?? alter table employee add constraint check_salary check

??? (salary between 1000 and 5000);

3.批改约束名

?? alter table department rename constraint pk_department to pk_department_deptno

4.删除约束

?? alter table employee drop constraint check_job

?? alter table department drop primary key cascade

三、拦阻 和激活约束

1.拦阻 约束

?? alter table employee disable novalidate constraint check_job

2.激活约束

A:应用 enable novalidate选项激活约束

?? alter table employee enable novalidate constraint check_job

B:应用 enable validate 选项激活约束

??? alter table employee enable validate constraint check_job

3.批改约束数据

A:成立 exceptions表

?? sql>@%oracle_home%\rdbms\admin\utlexcpt

B:激活约束

?? alter table employee enable validate constraint check_job exceptions into exceptions;

C:断定不满意约束正直的行

select job,rowid from employee where rowid in(select row_id from exceptions)

D:批改数据

?? update employee set job='CLERK' where rowid='AACP9AABAAAMPyAAA';

E:激活约束

?? alter table employee enable validate constraint check_job

四、应用延期约束反省

1.应用立即 反省（默认选项，不指定deferrable)

2.应用延期约束反省

?? 对当前事宜起作用：set constraint s_emp_fk deferred;

?? 对当前会话起作用：alter session set constraint=deferred;

五、表现约束信息

1.表现约束信息

?? select constraint_name,constraint_type,status,validated from dba_constraints

?? where owner='SCOTT' and table_name='EMPLOYEE'

2.表现约束列

?? select column_name from dba_cons_columns

?? where owner='SCOTT' and constraint_name='CHECK_JOB';