Oracle数据库约束

Oracle数据库是大家平时的工作中经常会用到的，本文将为大家带来Oracle数据库约束的讲解，希望对大家能够有所帮助。

最近一张表上有两列字段，要求这两列要么都有值，要么都为空，简单的table定义没办法实现这种要求，需要利用Oracle的constraint（约束）机制。约束主要是用来保证数据的完整性。

可以从TOAD的设置上，很容易看到约束分为4种，分别是主键（Primary Key），检查（Check），唯一性（Unique），外键（Foreign Key）。另外还有两种是NOT NULL和REF，REF就是其中的一列或者几列是另外一张表中的值。

下面是对着6中的详细介绍。

NOT NULL constraint prohibits a database value from being null.

Unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.

Primary Key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. It prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.

Foreign Key constraint requires values in one table to match values in another table.

Check constraint requires a value in the database to comply with a specified condition.

REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references.

对于我们的要求，符合的是Check，可以通过增加一个条件是( A is null and B is null ) or ( A is not null and B is not null) 的约束来实现。

对于约束，主要的状态有两种，一个是Status on Creation，也就是在表中增加数据或修改数据时是否使用约束，可选值是Enabled和Disabled；另外一种是Validation，它表示是否对表中现有的数据是否进行验证，可选值是Validate和NoValidate。

对于上面这两种状态，有4种组合，下面是对着四种的详细介绍。

ENABLE VALIDATE is the same as ENABLE. The constraint is checked and is guaranteed to hold for all rows.

ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be true for all rows. This allows existing rows to violate the constraint, while ensuring that all new or modified rows are valid.

In an ALTER TABLE statement, ENABLE NOVALIDATE resumes constraint checking on disabled constraints without first validating all data in the table.

DISABLE NOVALIDATE is the same as DISABLE. The constraint is not checked and is not necessarily true.

DISABLE VALIDATE disables the constraint, drops the index on the constraint, and disallows any modification of the constrained columns.

约束的设置还有一个延迟性设置，默认是非延迟的，也就是Initially Immediate，这种情况下任何的修改都会进行校验，另外一种是延迟的，也就是Intially Deferred，会在所有修改完成后commit的时候校验，进而引发回滚。