oracle约束

约束 Including Constraints

在数据库中使用约束(constraints)是为了在该数据库中实施所谓的"业务规则"其实就是防止非法信息进入数据库,满足管理员和应用开发人员所定义的规则集.

ORACLE使用完整性约束(integrity constraints)防止不合法的数据写入数据库,管理员和开发人员可以定义完整性规则,增强商业规则,限制数据表中的数据.如果一个DML语句执行的任何结果破坏了完整性约束,ORACLE就会回滚语句,返回错误信息.

约束是通过使用CREATE TABLE或ALTER TABLE语句生成的.(建立表时或者表建立后修改都可)如果相关的约束定义在单列上,可以在列这一级指定约束的定义;多列约束必须定义在数据表级,相关的列要在括号中指定,用逗号分隔.如果没有为约束提供一个名字,那么ORACLE会分配一个系统生成的唯一名字,以SYS_开头,你可以使用关键字CONSTRAINTS后面跟随相关的约束名字来为约束指定名字.

ORACLE支持五种类型的完整性约束
NOT NULL (非空)--防止NULL值进入指定的列,在单列基础上定义,默认情况下,ORACLE允许在任何列中有NULL值.
CHECK (检查)--检查在约束中指定的条件是否得到了满足.
UNIQUE (唯一)--保证在指定的列中没有重复值.在该表中每一个值或者每一组值都将是唯一的.
PRIMARY KEY (主键)--用来唯一的标识出表的每一行,并且防止出现NULL值,一个表只能有一个主键约束.
POREIGN KEY (外部键)--通过使用公共列在表之间建立一种父子(parent-child)关系,在表上定义的外部键可以指向主键或者其他表的唯一键.

约束定义存储在数据字典中,查询USER_CONSTRAINTS可以获得相关信息.

定义约束
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);
e.g.
CREATE TABLE employees
(employee_id NUMBER(6),
first_name VARCHAR2(20),
...
job_id VARCHAR2(10) NOT NULL,
CONSTRAINTS emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID));
列级的约束定义
column [CONSTRAINT constraint_name] constraint_type,
表级约束的定义
column,..
[CONSTRAINT constraint_name] constraint_type (column,...)

NOT NULL约束
只能定义在列级,不能定义在表级.
e.g.
CREATE TABLE employees
(employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL, //没有指定名字,将用系统SYS_命名
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL); //用户定义了名字

UNIQUE约束
用来保护一个表中的一个或者多个列没有任何两行在收到保护的列中具有重复的数据.ORACLE在唯一键列上自动生成一个唯一索引以实现唯一性
e.g.
CREATE TABLE employees
(employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
CONSTRAINT emp_email_uk UNIQUE(email));

PRIMARY KEY约束
唯一键的所有特征都适用于主键约束,只是在主键列中不允许有NULL值.一个表只能有一个主键.
e.g.
CREATE TABLE departments
(department_id NUMBER(4),
department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4),
CONSTRAINT dept_id_pk PRIMARY KEY(department_id));

FOREIGN KEY 约束
用来保护一个表中的一个或者多个列,它会通过一个主键主键或者唯一键保证对于每个非NULL值在数据库的其他地方都有一个数据可用.这个外部键就是在生成此约束的表(子表)中的一个或多个列,在父级表和子表中,相关列的数据类型必须匹配.外部键列和引用键(reference key)列可以位于相同的表中(自引用完整性约束).
e.g.
CREATE TABLE employees
(employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
deparment_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY(department_id) REFERENCES departments(department_id),
CONSTRAINT emp_email_uk UNIQUE(email));

上例中是在表级定义外部键约束,如果在列级定义,不同的是:
CREATE TABLE employees
(...,
department_id NUMBER(4) CONSTRAINT emp_deptid_fk REFERENCES departments(department_id),
...);
//没有关键字FOREIGN KEY
FOREIGN KEY约束还有两个关键字是
ON DELETE CASCADE --当删除所引用的父表记录时,删除子表中相关的记录
ON DELETE SET NULL--与上面不同的是删除时,转换子表中相关记录为NULL值
默认情况下,如果没有指定以上两个中任一,则父表中被引用的记录将不能被删除.

CHECK 约束
[CONSTRAINT ] CHECK ( )
这里CHECK子句中的CONDITION应该求值为一个布尔值结果,并且可以引用相同行中其他列的值;不能包含子查询,序列,环境函数(SYSDATE,UID,USER,USERENV)和伪列(ROWNUM,LEVEL,CURRVAL,NEXTVAL),一个列上可以定义多个CHECK约束,如果所定义的条件为FALSE,则语句将回滚.
CREATE TABLE employees
(...,
salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary>0),
...);

添加约束
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);
删除约束
ALTER TABLE employees
DROP CONSTRAINT emp_manager_fk;
ALTER TABLE departments
DROP PRIMARY KEY CASCADE; //由于departments被employees.department_id引用了
对于NOT NULL约束,用ALTER TABLE MODIFY子句来删除
ALTER TABLE employees MODIFY last_name NULL;
关闭约束
ALTER TABLE employees
DISABLE CONSTRAINT emp_emp_id_pk CASCADE; //如果没有被引用则不需CASCADE关键字
当你生成一个约束时,约束自动打开(除非你指定了DISABLE子句0,当用DISABLE关闭UNIQUE或者PRIMARY KEY约束时,ORACLE会自动删除相关的唯一索引,再次打开时,ORACLE又会自动建立的.
打开约束
ALTER TABLE employees
ENABLE CONSTRAINT emp_emp_id_pk; //注意,打开一个先前关闭的被引用的主键约束,并不能自动打开相关的外部键约束

可以从USER_CONSTRAINTS表和USER_CONS_COLUMNS视图中查询约束的信息
SELECT constraint_name,constraint_type,search_condition
FROM user_constraints
WHERE table_name='EMPLOYEES';

约束类型
C--CHECK和NOT NULL都算为C TYPE
P--PRIMARY KEY
R--REFERENTIAL INTEGRITY就是外部键约束
U--UNIQUE

SELECT constraint_name,column_name
FROM user_cons_columns
WHERE table_name='EMPLOYEES';