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

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

可以從TOAD的設(shè)置上，很容易看到約束分為4種，分別是主鍵（Primary Key），檢查（Check），唯一性（Unique），外鍵（Foreign Key）。另外還有兩種是NOT NULL和REF，REF就是其中的一列或者幾列是另外一張表中的值。

下面是對(duì)著6中的詳細(xì)介紹。

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.

對(duì)于我們的要求，符合的是Check，可以通過增加一個(gè)條件是( A is null and B is null ) or ( A is not null and B is not null) 的約束來實(shí)現(xiàn)。

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

對(duì)于上面這兩種狀態(tài)，有4種組合，下面是對(duì)著四種的詳細(xì)介紹。

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è)置還有一個(gè)延遲性設(shè)置，默認(rèn)是非延遲的，也就是Initially Immediate，這種情況下任何的修改都會(huì)進(jìn)行校驗(yàn)，另外一種是延遲的，也就是Intially Deferred，會(huì)在所有修改完成后commit的時(shí)候校驗(yàn)，進(jìn)而引發(fā)回滾。