Oracle 笔记（5） 约束

Oracle数据库使用约束（constraints）来防止无效的数据进入到表中，保护数据的实体完整性

约束规则定义在表级，如果表和表之间有从属关系，约束也可以防止表的删除

通过约束，强制用户在插入、更新或删除数据时必须遵循一定的规则

约束可以在创建表的时候定义，也可以在表创建之后定义

所有的约束存储在数据字典中，可以用USER_CONSTRAINTS数据字典表查看对一个表的约束的定义

 

--创建表时的同时定义约束的语法

CREATE TABLE [schema.]table

(col1 type1 [DEFAULT1]  [col1_constraint],

 col2 type1 [DEFAULT2]  [col2_constraint],

…

[table_constraint])

其中col1_constraint是给一个col1字段定义一部分的完整性约束，而table_constraint是给表table定义一部分的完整性约束

约束可以在两个级别上定义，分别是字段级别约束和表级别约束，其中字段级别约束只涉及一个单个的字段，能够定义完整性约束的任何类型；而表级别约束涉及一个或多个字段，不能定义NOT NULL这个约束

 

例如：现在仍然需要新建一个部门表depts_temp3，该部门表有四个字段，分别是部门编号dept_id、部门名称dept_name、部门经理名字manager_fname和所在地dept_loc。其中部门编号和部门名称不能为空，并且部门编号是该表的主键（约束名为：dept3_id_pk）

CREATE TABLE depts_temp3(

dept_id NUMBER(4) NOT NULL,

dept_name VARCHAR2(20) NOT NULL,

manager_fname VARCHAR2(14),

dept_loc VARCHAR2(50),

CONSTRAINT  dept3_id_pk  PRIMARY KEY(dept_id)

     );

约束一共有五种：

1.NOT NULL约束

NOT NULL约束又称非空约束，NOT NULL约束只能定义在字段，不能定义在表级别

2.UNIQUE约束

UNIQUE约束又称唯一键约束

要求字段或者字段的组合（键）的值是唯一的，定义UNIQUE约束的字段（或字段组合） 被称为唯一键（unique key）

唯一键约束允许输入空值，且包含空值的行可以是任意数目，因为空不等于任何事

UNIQUE约束既可以在字段级也可以在表级定义

例：如果需要给depts_temp3表增加一个部门描述的字段dept_desc，且此字段是唯一键，键名dept4_desc_uk

ALTER TABLE depts_temp3

        ADD(dept_desc VARCHAR(100) CONSTRAINT  dept4_desc_uk  UNIQUE)

3.PRIMARY KEY约束

主键和外键是设置表和表之间关联的对象，在多表查询时就是通过表的主键和外键的关系建立起表和表之间的连接。

PRIMARY KEY约束为表创建一个主键，每个表只能创建一个主键，主键是表的每一行的唯一性标识，该约束强制字段或字段组合的唯一性，并且确保作为主键一部分的字段不能包含空值

对于一个主键字段，UNIQUE索引被自动创建。主键可以定义在列级别，也可以定义在表级别，用表级别定义的主键通常是字段组合主键

4.FOREIGN KEY约束

即外键约束，FOREIGN KEY约束是引用完整性约束，指明一个字段或者字段的组合作为一个外键，这个外键和另一个表的主键（或唯一键）建立起一个关系。

一个外键值必须匹配一个在父表中存在的值，所以在子表中，不能在外键字段中输入一个没有引用的值；同时在父表中，不能删除这样的行，该行的主键正被子表所引用

5.CHECK约束

CHECK约束是一种灵活的约束，定义每行都必须满足的条件

一个字段可以定义多个CHECK约束

假设要求临时雇员表emps中的salary字段的值必须大于等于零，则可以通过下面的SQL语句，给emps中的salary字段添加约束

ALTER TABLE emps

    ADD CONSTRAINT emps_salary_min CHECK(salary >= 0)

该段SQL语句没有采用创建表时添加约束的形式，而是在表创建以后直接添加约束。其语法形式和修改表字段的语法形式类似，只是这里操作的是约束而不是字段

 

--给表直接添加约束的语法形式如下：

ALTER TABLE table

ADD  [CONSTRAINT cname]  type(column)[DISABLE]

其中cname是约束名，type是约束类型有3个选项

（PRIMARY KEY | UNIQUE | CHECK），

column是受影响的字段名,DISABLE是指建立的约束不启用

其中约束名在语法中是一个可选项，如果不命名约束，系统会产生一个约束名，不过还是建议命名约束

--启用约束

ALTER TABLE tb_supplier ENABLE CONSTRAINT check_tb_supplier_id;

--禁用约束

ALTER TABLE tb_supplier DISABLE CONSTRAINT check_tb_supplier_id;

--删除约束的语法形式如下：

 ALTER TABLE table DROP  PRIMARY KEY | UNIQUE(column) | CONSTRAINT cname [CASCADE]

为了删除约束，可以先从USER_CONSTRAINTS数据字典视图中确定约束的名字，然后使用带DROP子句的ALTER TABLE语句

DROP子句的CASCADE选项导致任何与其相依赖的约束也被删除

--要删除depts_temp3表中的dept3_desc_uk约束，其SQL语句为：

ALTER TABLE depts_temp3 DROP  CONSTRAINT dept3_desc_uk

--要删除depts_temp3表上的主键约束（dept_id），并且删除相关联的emps表上dept_id字段上的外键约束

ALTER TABLE depts_temp3 DROP  PRIMARY KEY CASCADE