oracle之约束（constraint）

1 什么是约束（constraint）

数据库约束是对表中的数据进行进一步的限制,保证数据的正确性、有效性和完整性。

2 注意事项

• 如果不指定约束名 ，Oracle server 自动按照 SYS_Cn 的格式指定约束名
• 创建和修改约束:
• 建表的同时
• 建表之后
• 可以在表级或列级定义约束
• 可以通过数据字典视图查看约束

3 表级约束和列级约束

作用范围：

• 列级约束只能作用在一个列上
• 表级约束可以作用在多个列上（当然表级约束也可以作用在一个列上）
• 定义方式：列约束必须跟在列的定义后面，表约束不与列一起，而是单独定义。
• 非空(not null) 约束只能定义在列上

定义约束举例

语法

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));

定义约束举例（列级）

语法

column [CONSTRAINT constraint_name] constraint_type,

举例

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

NOT NULL 约束

NOT NULL 约束举例

• 保证列值不能为空
• 只能定义在列级

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 
                   CONSTRAINT emp_hire_date_nn--用户命名
                   NOT NULL,

可以在PL/SQLDEV的My objects-Tables-employees-Check constraints中查看

UNIQUE 约束（唯一约束，允许出现多个空值：NULL。）

UNIQUE 约束(举例) 可以定义在表级或列级

CREATE TABLE employees(
    employee_id      NUMBER(6),
    last_name        VARCHAR2(25) UNIQUE,--系统命名
    email            VARCHAR2(25),
    salary           NUMBER(8,2),
    commission_pct   NUMBER(2,2),
    hire_date        DATE NOT NULL,
...  
    CONSTRAINT emp_email_uk UNIQUE(email));--用户命名
    可以声明在email后面：CONSTRAINT emp_email_uk UNIQUE,也可以如此末处声明。

PRIMARY KEY 约束

PRIMARY KEY 约束（举例）可以定义在表级或列级

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 约束

FOREIGN KEY 约束（举例）可以定义在表级或列级:

CREATE TABLE employees(
    employee_id      NUMBER(6),
    last_name        VARCHAR2(25) NOT NULL,
    email            VARCHAR2(25),
    salary           NUMBER(8,2),
    commission_pct   NUMBER(2,2),
    hire_date        DATE NOT NULL,
...
    department_id    NUMBER(4),
    CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
      REFERENCES departments(department_id),
    CONSTRAINT emp_email_uk UNIQUE(email));

FOREIGN KEY	约束的关键字
FOREIGN KEY	在表级指定子表中的列
REFERENCES	标示在父表中的列
ON DELETE CASCADE(级联删除)	当父表中的列被删除时，子表中相对应的列也被删除
ON DELETE SET NULL(级联置空)	子表中相应的列置空

CREATE TABLE emp(
id NUMBER(6) PRIMARY KEY,
name VARCAHER2(25) UNIQUE,
sal NUMBER(8,2),
dept_id number(4),
constraint dept_fk foreign key(dept_id) rederence dept(dept_id) on delete cascade
)

CHECK 约束(定义每一行必须满足的条件)

..., salary	NUMBER(2)
     CONSTRAINT emp_salary_min  
            CHECK (salary > 0),...

CREATE TABLE emp(
id NUMBER(6) PRIMARY KEY,
name VARCAHER2(25) UNIQUE,
sal NUMBER(8,2) CHECK(sal > 0 and sal < 1000000),
dept_id number(4),
constraint dept_fk foreign key(dept_id) rederence dept(dept_id) on delete set null
)

4 添加约束

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

添加约束的语法

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

添加和删除约束

以create table emp as select * from employees;

Alter table emp modify(empname varchar2(50) not null);

添加约束

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

从表 EMPLOYEES 中删除约束

ALTER TABLE employees
DROP CONSTRAINT  emp_manager_fk;
Table altered.

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

ALTER TABLE	employees
DISABLE CONSTRAINT	emp_emp_id_pk;
Table altered.

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

ALTER TABLE	employees
ENABLE CONSTRAINT emp_emp_id_pk;
Table altered.

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

查询约束

查询数据字典视图 USER_CONSTRAINTS

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

查询定义约束的列

查询数据字典视图 USER_CONS_COLUMNS

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

总结

约束的类型总结
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK