Oracle学习----约束

什么是约束？

约束是表级别的强制执行的规则，如果一张表有从属关系，约束阻止表删除。

下面的约束类型是有效的：

— NOT NULL

— UNIQUE

— PRIMARY KEY

— FOREIGN KEY

— CHECK

约束指导方针

----命名一个约束或者由Oracle服务器生成一个名字，使用SYS_Cn格式。

----创建一个约束或者：

----在表创建的同时创建

----在表创建之后创建

-----在列或者在表级定义一个约束

-----在数据字典中查看约束

定义约束

CREATE TABLE  [schema.]table  (

column  datatype [DEFAULT  expr]  [column_constraint],

…

[table_constraint]  [,…]);

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

在列级定义

CREATE TABLE  employees(

employee_id  NUMBER(6),

last_name    VARCHAR2(25)  NOT NULL,(系统命名)

salary        NUMBER(8,2),

commission_pct  NUMBER(8,2),

hire_date     DATE CONSTRAINT  emp_hire_date_nn  NOT NULL,(自己命名)

….

);

唯一性约束

在表级或者列级定义

CREATE TABLE  employees  (

employee_id    NUMBER(6),

last_name      VARCHAR2(25)  NOT NULL,

email          VARCHAR2(25),

salary          NUMBER(8,2),

commission     NUMBER(2,2),

hire_date       DATE NOT  NULL,

……

CONSTRAINT  emp_email_uk UNIQUE(email)

);

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

可以在表级或者列级定义

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:将依靠的外键值转换为空值

CHECK约束

----定义每行必须满足的条件

----不允许下面的表达式：

   — 涉及到CURRVAL, NEXTVAL, LEVEL和ROWNUM假列

   — 调用SYSDATE,UID,USER和USERENV函数

— 查询指向其他行的值

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

添加约束

使用ALTER TABLE语句进行：

1）  增加或者删除一个约束，但是不能修改它的结构

2）  激活或者屏蔽约束

3）  使用MODIFY子句增加Not Null约束

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 CONDTRAINT  emp_manager_fk;

删除雇员表中的主键约束，并同时删除相关的在EMPLOYEES.DEPARTMENT_ID的外键约束

ALTER TABLE  departments

DROP PRIMARY  KEY  CASCADE;

禁止约束

执行ALTER  TABLE语句的DISABLE子句解除完整性约束

应用CASCADE选项屏蔽附属完整性约束

ALTER TABLE  employees

DISABLE CONSTRAINT  emp_emp_id_pk  CASCADE;

激活约束

通过使用ENABLE子句激活当前在表中屏蔽的完整性约束

如果你激活了UNIQUE关键字或者PRIMARY KEY约束，那么UNIQUE或者PRIMARY KEY索引是会自动创建的

约束层叠

CASCADE  CONSTRAINTS子句是和DROP  COLUMN子句单独使用的

CASCADE  CONSTRAINTS子句会删除所有的涉及到定义在删除列的主键和唯一键的相关完整性约束

CASCADE  CONSTRAINTS子句也会删除定义在要删除列上的所有层叠约束

eg:

ALTER TABLE  test1

DROP (pk)  CASCADE  CONSTRAINTS;

ALTER TABLE  test1

DROP (pk,fk,col1)  CASCADE  CONSTRAINTS;

查看约束

查询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’;