Oracle數(shù)據(jù)庫是大家平時的工作中經(jīng)常會用到的，本文將為大家?guī)鞳racle數(shù)據(jù)庫約束的講解，希望對大家能夠有所幫助。

最近一張表上有兩列字段，要求這兩列要么都有值，要么都為空，簡單的table定義沒辦法實現(xiàn)這種要求，需要利用Oracle的constraint（約束）機制。約束主要是用來保證數(shù)據(jù)的完整性。

可以從TOAD的設(shè)置上，很容易看到約束分為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) 的約束來實現(xiàn)。

對于約束，主要的狀態(tài)有兩種，一個是Status on Creation，也就是在表中增加數(shù)據(jù)或修改數(shù)據(jù)時是否使用約束，可選值是Enabled和Disabled；另外一種是Validation，它表示是否對表中現(xiàn)有的數(shù)據(jù)是否進行驗證，可選值是Validate和NoValidate。

對于上面這兩種狀態(tài)，有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.

約束的設(shè)置還有一個延遲性設(shè)置，默認是非延遲的，也就是Initially Immediate，這種情況下任何的修改都會進行校驗，另外一種是延遲的，也就是Intially Deferred，會在所有修改完成后commit的時候校驗，進而引發(fā)回滾。