Oracle PL/SQL语言初级教程之完整性约束

快来打我* 2022-09-20 08:44 171阅读 0赞

**完整性约束**  
  
　　完整性约束用于增强数据的完整性，Oracle提供了5种完整性约束：  
  
　　　 Check  
　　　 NOT NULL  
　　　 Unique  
　　　 Primary  
　　　 Foreign key  
  
　　完整性约束是一种规则，不占用任何数据库空间。完整性约束存在数据字典中，在执行SQL或PL/SQL期间使用。用户可以指明约束是启用的还是禁用的，当约束启用时，他增强了数据的完整性，否则，则反之，但约束始终存在于数据字典中。  
  
　　禁用约束，使用ALTER语句

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;</td> 
  </tr> 
 </tbody> 
</table>

或

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TABLE policies DISABLE CONSTRAINT chk_gender</td> 
  </tr> 
 </tbody> 
</table>

如果要重新启用约束：

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TABLE policies ENABLE CONSTRAINT chk_gender</td> 
  </tr> 
 </tbody> 
</table>

删除约束

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TABLE table_name DROP CONSTRAINT constraint_name</td> 
  </tr> 
 </tbody> 
</table>

或

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TABLE policies DROP CONSTRAINT chk_gender;</td> 
  </tr> 
 </tbody> 
</table>

**Check 约束**  
  
　　在数据列上Check 约束需要 一个特殊的布尔条件或者将数据列设置成TRUE，至少一个数据列的值是NULL，Check约束用于增强表中数据内容的简单的商业规则。用户使用Check约束保证数据规则的一致性。Check约束可以涉及该行同属Check约束的其他数据列但不能涉及其他行或其他表，或调用函数SYSDATE,UID,USER,USERENV。如果用户的商业规则需要这类的数据检查，那么可以使用触发器。Check约束不保护LOB数据类型的数据列和对象、嵌套表、VARRY、ref等。单一数据列可以有多个Check约束保护，一个Check约束可以保护多个数据列。  
  
　　创建表的Check约束使用CREATE TABLE语句，更改表的约束使用ALTER TABLE语句。  
  
　　语法：

<table> 
 <tbody> 
  <tr> 
   <td>CONSTRAINT [constraint_name] CHECK (condition);</td> 
  </tr> 
 </tbody> 
</table>

Check约束可以被创建或增加为一个表约束，当Check约束保护多个数据列时，必须使用表约束语法。约束名是可选的并且如果这个名字不存在，那么oracle将产生一个以SYS\_开始的唯一的名字。  
  
　　例：

<table> 
 <tbody> 
  <tr> 
   <td>CREATE TABLE policies<br>(policy_id NUMBER,<br>holder_name VARCHAR2(40),<br>gender VARCHAR2(1) constraint chk_gender CHECK (gender in ('M','F'),<br>marital_status VARCHAR2(1),<br>date_of_birth DATE,<br>constraint chk_marital CHECK (marital_status in('S','M','D','W'))<br>);<br></td> 
  </tr> 
 </tbody> 
</table>

**NOT NULL约束**  
  
　　NOT NULL约束应用在单一的数据列上，并且他保护的数据列必须要有数据值。缺省状况下，ORACLE允许任何列都可以有NULL值。某些商业规则要求某数据列必须要有值，NOT NULL约束将确保该列的所有数据行都有值。  
  
　　例：

<table> 
 <tbody> 
  <tr> 
   <td>CREATE TABLE policies<br>(policy_id NUMBER,<br>holder_name VARCHAR2(40) NOT NULL,<br>gender VARCHAR2(1),<br>marital_status VARCHAR2(1),<br>date_of_birth DATE NOT NULL<br>);</td> 
  </tr> 
 </tbody> 
</table>

对于NOT NULL的ALTER TABLE语句与其他约束稍微有点不同。

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TABLE policies MODIFY holder_name NOT NULL</td> 
  </tr> 
 </tbody> 
</table>

**唯一性约束(Unique constraint)**  
  
　　唯一性约束可以保护表中多个数据列，保证在保护的数据列中任何两行的数据都不相同。唯一性约束与表一起创建，在唯一性约束创建后，可以使用ALTER TABLE语句修改。  
  
　　语法：

<table> 
 <tbody> 
  <tr> 
   <td>column_name data_type CONSTRAINT constraint_name UNIQUE </td> 
  </tr> 
 </tbody> 
</table>

如果唯一性约束保护多个数据列，那么唯一性约束要作为表约束增加。语法如下：

<table> 
 <tbody> 
  <tr> 
   <td>CONSTRAINT constraint_name (column) UNIQUE USING INDEX TABLESPACE (tablespace_name) STORAGE (stored clause)</td> 
  </tr> 
 </tbody> 
</table>

唯一性约束由一个B-tree索引增强，所以可以在USING子串中为索引使用特殊特征，比如表空间或存储参数。CREATE TABLE语句在创建唯一性约束的同时也给目标数据列建立了一个唯一的索引。

<table> 
 <tbody> 
  <tr> 
   <td>CREATE TABLE insured_autos<br>(policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY,<br>vin VARCHAR2(10),<br>coverage_begin DATE,<br>coverage_term NUMBER,<br>CONSTRAIN unique_auto UNIQUE (policy_id,vin) USING INDEX TABLESPACE index STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0)<br>);</td> 
  </tr> 
 </tbody> 
</table>

用户可以禁用未以性约束，但他仍然存在，禁用唯一性约束使用ALTER TABLE 语句

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TABLE insured_autos DISABLE CONSTRAIN unique_name;</td> 
  </tr> 
 </tbody> 
</table>

删除唯一性约束，使用ALTER TABLE....DROP CONSTRAIN语句

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TABLE insured_autos DROP CONSTRAIN unique_name;</td> 
  </tr> 
 </tbody> 
</table>

注意用户不能删除在有外部键指向的表的唯一性约束。这种情况下用户必须首先禁用或删除外部键（foreign key)。  
  
　　删除或禁用唯一性约束通常同时删除相关联的唯一索引，因而降低了数据库性能。经常删除或禁用唯一性约束有可能导致丢失索引带来的性能错误。要避免这样错误，可以采取下面的步骤：  
  
　　1、在唯一性约束保护的数据列上创建非唯一性索引。  
  
　　2、添加唯一性约束  
  
　　 **主键(Primary Key)约束**  
  
　　表有唯一的主键约束。表的主键可以保护一个或多个列，主键约束可与NOT NULL约束共同作用于每一数据列。NOT NULL约束和唯一性约束的组合将保证主键唯一地标识每一行。像唯一性约束一样，主键由B-tree索引增强。  
  
　　创建主键约束使用CREATE TABLE语句与表一起创建，如果表已经创建了，可以使用ALTER TABLE语句。

<table> 
 <tbody> 
  <tr> 
   <td>CREATE TABLE policies<br>(policy_id NUMBER CONSTRAINT pk_policies PRIMARY KEY,<br>holder_name VARCHAR2(40),<br>gender VARCHAR2(1),<br>marital_status VARCHAR2(1),<br>date_of_birth DATE<br>);</td> 
  </tr> 
 </tbody> 
</table>

与唯一性约束一样，如果主键约束保护多个数据列，那么必须作为一个表约束创建。

<table> 
 <tbody> 
  <tr> 
   <td>CREATE TABLE insured_autos<br>(policy_id NUMBER,<br>vin VARCHAR2(40),<br>coverage_begin DATE,<br>coverage_term NUMBER,<br>CONSTRAINT pk_insured_autos PRIMARY KEY (policy_id,vin)<br>USING INDEX TABLESPACE index<br>STORAGE (INITIAL 1M NEXT 10M PCTINCREASE 0)<br>);</td> 
  </tr> 
 </tbody> 
</table>

禁用或删除主键必须与ALTER TABLE 语句一起使用

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TABLE policies DROP PRIMARY KEY;</td> 
  </tr> 
 </tbody> 
</table>

或

<table> 
 <tbody> 
  <tr> 
   <td>ALTER TABLE policies DISABLE PRIMARY KEY;</td> 
  </tr> 
 </tbody> 
</table>

**外部键约束（Foreign key constraint)**  
  
　　外部键约束保护一个或多个数据列，保证每个数据行的数据包含一个或多个null值，或者在保护的数据列上同时拥有主键约束或唯一性约束。引用（主键或唯一性约束）约束可以保护同一个表，也可以保护不同的表。与主键和唯一性约束不同外部键不会隐式建立一个B-tree索引。在处理外部键时，我们常常使用术语父表（parent table)和子表（child table),父表表示被引用主键或唯一性约束的表，子表表示引用主键和唯一性约束的表。  
  
　　创建外部键使用CREATE TABLE语句，如果表已经建立了，那么使用ALTER TABLE语句。

<table> 
 <tbody> 
  <tr> 
   <td>CREATE TABLE insured_autos <br>(policy_id NUMBER CONSTRAINT policy_fk<br>REFERENCE policies(policy_id<br>ON DELETE CASCADE,<br>vin VARCHAR2(40),<br>coverage_begin DATE,<br>coverage_term NUMBER,<br>make VARCHAR2(30),<br>model VARCHAR(30),<br>year NUMBER,<br>CONSTRAIN auto_fk FROEIGN KEY (make,model,year)<br>REFERENCES automobiles (make,model,year)<br>ON DELETE SET NULL<br>);<br></td> 
  </tr> 
 </tbody> 
</table>

ON DELETE子串告诉ORACLE如果父纪录（parent record)被删除后，子记录做什么。缺省情况下禁止在子记录还存在的情况下删除父纪录。  
  
　　 **外部键和NULL值**  
  
　　在外部键约束保护的数据列中NULL值的处理可能产生不可预料的结果。ORACLE 使用ISO standar Match None规则增强外部键约束。这个规则规定如果任何外部键作用的数据列包含有一个NULL值，那么任何保留该键的数据列在父表中没有匹配值。  
  
　　比如，在父表AUTOMOBILES中，主键作用于数据列MAKE，MODEL，YEAR上，用户使用的表INSURED\_AUTOS有一个外部约束指向AOTOMOBILES，注意在INSURES\_AUTOS中有一数据行的MODEL列为NULL值，这一行数据已经通过约束检查，即使MAKE列也没有显示在父表AUTOMOBILES中，如下表：  
  
　　表1 AUTOMOBILES

<table> 
 <tbody> 
  <tr> 
   <td>MAKE </td> 
   <td>MODEL </td> 
   <td>YEAR</td> 
  </tr> 
  <tr> 
   <td>Ford </td> 
   <td>Taurus</td> 
   <td>2000</td> 
  </tr> 
  <tr> 
   <td>Toyota</td> 
   <td>Camry </td> 
   <td>1999</td> 
  </tr> 
 </tbody> 
</table>

表2 INSURED\_AUTOS

<table> 
 <tbody> 
  <tr> 
   <td>POLICY_ID</td> 
   <td>MAKE </td> 
   <td>MODEL </td> 
   <td>YEAR</td> 
  </tr> 
  <tr> 
   <td>576 </td> 
   <td>Ford </td> 
   <td>Taurus </td> 
   <td>2000</td> 
  </tr> 
  <tr> 
   <td>577 </td> 
   <td>Toyota</td> 
   <td>Camry </td> 
   <td>1999 </td> 
  </tr> 
  <tr> 
   <td>578 </td> 
   <td>Tucker </td> 
   <td>NULL </td> 
   <td>1949</td> 
  </tr> 
 </tbody> 
</table>

**延迟约束检验（Deferred Constraint Checking）**  
  
　　约束检验分两种情况，一种是在每一条语句结束后检验数据是否满足约束条件，这种检验称为立即约束检验（immediately checking）,另一种是在事务处理完成之后对数据进行检验称之为延迟约束检验。在缺省情况下Oracle约束检验是立即检验（immediately checking),如果不满足约束将先是一条错误信息，但用户可以通过SET CONSTRAINT语句选择延迟约束检验。语法如下：

<table> 
 <tbody> 
  <tr> 
   <td>SET CONSTRAINT constraint_name|ALL DEFEERRED|IMMEDIATE --;</td> 
  </tr> 
 </tbody> 
</table>