[Oracle数据库学习]十、约束

D6

什么是约束

约束是表级的强制规定，放置在表中删除有关联关系的数据。

可以在表级或列级定义约束；
可以通过数据字典视图查看约束。

约束的类型

NOT NULL：非空
UNIQUE：唯一
PRIMARY KEY：主键
FOREIGN KEY：外键
CHECK：检查

 

约束的命名

如果不指定约束名，Oracle Server自动按照SYS_Cn的格式指定约束名

 

约束的创建时间

1）建表的同时
2）建表之后

 

定义约束

CREATE TABLE [schema.]table
    (column datatype [DEFAULT expr]
    [column_constraint],
    ...
    [table_constraint][,...]);

示例：

CREATE TABLE employees
    (employee_id NUMBER(6),
     first_name VARCHAR2(20),
     ...
     job_id VARCHAR2(10) NOT NULL,
     CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID)
    );

注：job_id列的约束，employees表的主键约束

 

列级

column [CONSTRAINT constraint_name] constraint_type,

 

表级

column,...
    [CONSTRAINT constraint_name] constraint_type
    (column, ...),

 

NOT NULL约束

保证列值不能为空，且只能定义在列级。

create table employees
(
  employee_id int,
  employee_name char(100),
  department_id int,
  job_id varchar2(100) NOT NULL,
  salary float,
  commission_pct float,
  hire_date date
    CONSTRAINT emp_hire_date_nn NOT NULL,
  description char(255),
  manager_id int
);

注：job_id处非空约束采用系统命名，hire_date处用户命名。

 

UNIQUE约束

定义在列级

employee_name唯一性约束：

create table employees
(
  employee_id int,
  employee_name char(100)
    CONSTRAINT emp_employee_name_uk UNIQUE,
  department_id int,
  job_id varchar2(100) NOT NULL,
  salary float,
  commission_pct float,
  hire_date date
    CONSTRAINT emp_hire_date_nn NOT NULL,
  description char(255),
  manager_id int
);

 

定义在表级

create table employees
(
  employee_id int,
  employee_name char(100),
  department_id int,
  job_id varchar2(100) NOT NULL,
  salary float,
  commission_pct float,
  hire_date date
    CONSTRAINT emp_hire_date_nn NOT NULL,
  description char(255),
  manager_id int,
  CONSTRAINT emp_employee_name_uk UNIQUE(employee_name)
);

 

PRIMARY KEY约束

定义在列级

create table departments
(
  department_id int PRIMARY KEY,
  department_name varchar2(300),
  location_id int
);

 

定义在表级

create table departments
(
  department_id int,
  department_name varchar2(300),
  location_id int,
  CONSTRAINT dept_id_pk PRIMARY KEY(department_id)
);

 

FOREIGN KEY约束

定义在列级

create table employees
(
  employee_id int,
  employee_name char(100),
  department_id int REFERENCES departments(department_id),
  job_id varchar2(100) NOT NULL,
  salary float,
  commission_pct float,
  hire_date date CONSTRAINT emp_hire_date_nn NOT NULL,
  description char(255),
  manager_id int,
  CONSTRAINT emp_employee_name_uk UNIQUE(employee_name)
);

 

定义在表级

create table employees
(
  employee_id int,
  employee_name char(100),
  department_id int,
  job_id varchar2(100) NOT NULL,
  salary float,
  commission_pct float,
  hire_date date CONSTRAINT emp_hire_date_nn NOT NULL,
  description char(255),
  manager_id int,
  CONSTRAINT emp_employee_name_uk UNIQUE(employee_name),
  CONSTRAINT emp_department_id_fk FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

 

FOREIGN KEY约束的关键字

FOREIGN KEY：在表级指定子表中的列；
REFERENCES：标示在父表中的列；
ON DELETE CASCADE：当父表中的列被删除时，子表中相对应的列也被删除；
ON DELETE SET NULL：当父表中的列被删除时，子表中相应的列置空。

 

CHECK约束

定义每一行必须满足的条件。

以下的表达式是不允许的：
1）出现CURRVAL, NEXTVAL, LEVEL和ROWNUM伪列；
2）使用SYSDATE, UID, USER和USERENV函数；
3）在查询中涉及到其它列的值。

create table employees
(
  employee_id int,
  employee_name char(100),
  department_id int,
  job_id varchar2(100) NOT NULL,
  salary float CONSTRAINT emp_salary_min CHECK (salary > 0),
  commission_pct float,
  hire_date date CONSTRAINT emp_hire_date_nn NOT NULL,
  description char(255),
  manager_id int,
  CONSTRAINT emp_employee_name_uk UNIQUE(employee_name),
  CONSTRAINT emp_department_id_fk FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

注：salary列的check约束：emp_salary_min 

 

ALTER TABLE添加约束

使用ALTER TABLE语句：

添加或删除约束, 但是不能修改约束;
有效化或无效化约束;
添加NOT NULL约束要使用MODIFY语句。

 

添加约束

ALTER TABLE table
ADD [CONSTRAINT constraint] type (column);

 

ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk 
FOREIGN KEY(manager_id) REFERENCES employees(employee_id);

 

删除约束

删除约束

ALTER TABLE employees
DROP CONSTRAINT emp_manager_fk;

 

使用CASCADE选项删除约束

ALTER TABLE departments
DROP PRIMARY KEY CASCADE;

 

无效化约束

在ALTER TABLE语句中使用DISABLE子句将约束无效化。

ALTER TABLE employees
DISABLE CONSTRAINT emp_employee_id_pk;

 

可以使用CASCADE选项将相关的约束也无效化。

ALTER TABLE employees
DISABLE CONSTRAINT emp_employee_id_pk CASCADE;

 

激活约束

使用ENABLE子句可将当前无效的约束激活。

ALTER TABLE employees
ENABLE CONSTRAINT emp_employee_id_pk;

当定义或激活UNIQUE或PRIMARY KEY约束时，系统会自动创建UNIQUE或PRIMARY KEY索引。

 

级联约束

在DROP COLUMN子句中使用CASCADE CONSTRAINTS子句：
1）在删除表的列时，CASCADE CONSTRAINTS子句指定将相关的约束一起删除；
2）在删除表的列时，CASCADE CONSTRAINTS子句同时也删除多列约束。

ALTER TABLE test1 
DROP (pk) CASCADE CONSTRAINTS;

 

查询约束

查询约束

查询数据字典视图USER_CONSTRAINTS。

SELECT constraint_name, constraint_type,search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

CONSTRAINT_NAME	CONSTRAINT_TYPE	SEARCH_CONDITION
SYS_C006996	C	"JOB_ID" IS NOT NULL
EMP_HIRE_DATE_NN	C	"HIRE_DATE" IS NOT NULL
SYS_C006998	P	(null)
EMP_EMPLOYEE_NAME_UK	U	(null)
EMP_DEPARTMENT_ID_FK	R	(null)

 

查询定义约束的列

查询数据字典视图USER_CONS_COLUMNS。

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

CONSTRAINT_NAME	COLUMN_NAME
SYS_C006998	JOB_ID
EMP_HIRE_DATE_NN	HIRE_DATE
EMP_EMPLOYEE_NAME_UK	EMPLOYEE_NAME
EMP_DEPARTMENT_ID_FK	DEPARTMENT_ID

 

总结：

本节介绍约束相关内容：

1）约束类型：NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK；

2）创建约束：CREATE TABLE、ALTER TABLE；

3）ALTER TABLE时：ADD、DROP、ENABLE、DISABLE；

4）级联约束：CASCADE CONSTRAINTS子句；

5）查询约束：USER_CONSTRAINTS（约束）、USER_CONS_COLUMNS（定义了约束的列）。