Oracle数据库约束

数据库 Oracle
Oracle数据库是甲骨文公司的一款关系数据库管理系统。到目前仍在数据库市场上占有主要份额。下文中主要为大家讲解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.

toad create constraint step 1

对于我们的要求,符合的是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。

toad create constraint step 2

对于上面这两种状态,有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的时候校验,进而引发回滚。

 

责任编辑:迎迎 来源: 中国IT实验室
相关推荐

2011-08-10 15:38:12

ConstraintOracle

2011-07-20 12:34:49

SQLite数据库约束

2011-08-10 16:01:11

OracleConstraint

2009-03-23 10:11:59

Oracle数据库唯一约束

2011-03-10 13:24:26

2010-04-23 09:23:44

Oracle 数据库

2015-08-21 12:59:38

Oracle数据库

2011-03-16 08:54:45

Oracle数据库索引

2011-05-19 13:25:14

Oracle数据库

2011-08-18 11:18:25

Oracle唯一约束唯一索引

2011-04-11 09:39:46

Oracle约束

2023-09-08 08:44:09

Oracle数据库

2011-03-29 10:47:49

ORACLE数据库

2010-04-19 16:01:54

2010-04-14 15:14:11

Oracle数据库

2010-06-17 12:59:07

Oracle

2010-04-02 13:59:08

Oracle数据库

2011-03-07 13:30:53

Oracle数据库

2011-05-24 14:27:42

2016-08-23 14:25:19

MySQL约束数据库
点赞
收藏

51CTO技术栈公众号