Oracle约束官方文档--总览、非空约束、唯一性约束

最新推荐文章于 2021-04-07 03:41:34 发布

O-0-O

最新推荐文章于 2021-04-07 03:41:34 发布

阅读量316

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

Types of Integrity Constraints

Oracle Database enables you to apply constraints both at the table and column level.

A constraint specified as part of the definition of a column or attribute is an inline specification. A constraint specified as part of the table definition is an out-of-line specification.

A key is the column or set of columns included in the definition of certain types of integrity constraints. Keys describe the relationships between the tables and columns of a relational database. Individual values in a key are called key values.

The following table describes the types of constraints. Each can be specified either inline or out-of-line, except for NOT NULL, which must be inline.

来自 <https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-integrity.html#GUID-1C9665AD-A444-4AFB-984F-6385FCBEA64E>

完整性(Integrity)约束(Constraint)的类型

Oracle数据库允许你在表级和列级应用约束

作为列或属性定义(definition)的一部分的约束是内联规范(specification)。作为表定义的一部分的约束是越界规范（out-of-line不知道怎么解释）。

包含在明确完整性约束中的单列或多列是一个键。在关系型数据库中多个键描述表和列之间的关系。在一个键中的单独的值（可能为一列，可能为多列组合）叫做键值。

下面的表格介绍约束的类型。每一中约束都可以被指定(specified)为内联或脱机规范，除了NOT NULL约束。NOT NULL约束只能符合内联规范。

Table 5-1 Types of Integrity Constraints

Constraint Type	Description	See Also
NOT NULL	Allows or disallows inserts or updates of rows containing a null in a specified column.	"NOT NULL Integrity Constraints"
Unique key	Prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.	"Unique Constraints"
Primary key	Combines a NOT NULL constraint and a unique constraint. It prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.	"Primary Key Constraints"
Foreign key	Designates a column as the foreign key and establishes a relationship between the foreign key and a primary or unique key, called the referenced key.	"Foreign Key Constraints"
Check	Requires a database value to obey a specified condition.	"Check Constraints"
REF	Dictates types of data manipulation allowed on values in a REF column and how these actions affect dependent values. In an object-relational database, a built-in data type called a REF encapsulates a reference to a row object of a specified object type. Referential integrity constraints on REF columns ensure that there is a row object for the REF.	Oracle Database Object-Relational Developer's Guide to learn about REFconstraints

NOT NULL约束：非空约束

允许或不允许在指定列中插入或更新包含空值的行

Unique Key约束：唯一约束

禁止(Prohibits) 多个(倍数multiple)在同一个列中或多个结合(combination)的列中的行具有相同的值，但是允许一些为空的值（即空值无法做判断，NULL = NULL 是伪命题）

Primary Key约束：主键约束

结合非空约束和唯一键约束。主键禁止一个或多个结合的列中具有相同的值，并且禁止空值

Foreign Key约束：外键约束

指定(Designates)一个列作为外键并且建立(establishes)一个关系在外键和一个主键或唯一键（被称为被引用键）

将列指定为外键，并和被称为被引用键的主键或唯一键之间建立关系

Check约束：检查约束

要求数据库的值遵从(obey)一个特殊的条件

REF约束：

描述引用列(REF column)中的值允许的数据操作(manipulation)类型以及这些操作如何影响依赖的数据的。

在一个对象-关系型数据库中，一个被称为REF的内建的数据类型，封装(encapsulates)了一个对(指定对象类型的)行对象的引用。

引用列上的完整性约束确保了引用有一个行对象。

NOT NULL Integrity Constraints

A NOT NULL constraint requires that a column of a table contain no null values. A null is the absence of a value. By default, all columns in a table allow nulls.

NOT NULL constraints are intended for columns that must not lack values. For example, the hr.employees table requires a value in the email column. An attempt to insert an employee row without an email address generates an error:

SQL> INSERT INTO hr.employees (employee_id, last_name) values (999, 'Smith');
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMAIL")

You can only add a column with a NOT NULL constraint if the table does not contain any rows or if you specify a default value.

NOT NULL完整性约束

一个NOT NULL约束要求表中包含的列不能有空值。null是缺少值。默认情况下，表中所有的列都允许空值。

NOT NULL约束被预期(intended)/用于不能缺少(lack)值的列。例如，hr.employees表要求一个email列值。我们尝试(attempt)往employee表中添加数据却不添加email地址，生成一个错误：

ORA-01400：不能将NULL插入HR.EMPLOYEES表的EMAIL列。

You can only add a column with a NOT NULL constraint if the table does not contain any rows or if you specify a default value.

这段话不知道该怎么理解。。。

Unique Constraints

A unique key constraint requires that every value in a column or set of columns be unique. No rows of a table may have duplicate values in a single column (the unique key) or set of columns (the composite unique key) with a unique key constraint.

Note:

The term key refers only to the columns defined in the integrity constraint. Because the database enforces a unique constraint by implicitly creating or reusing an index on the key columns, the term unique key is sometimes incorrectly used as a synonym for unique key constraint or unique index.

Unique key constraints are appropriate for any column where duplicate values are not allowed. Unique constraints differ from primary key constraints, whose purpose is to identify each table row uniquely, and typically contain values that have no significance other than being unique. Examples of unique keys include:

A customer phone number, where the primary key is the customer number
A department name, where the primary key is the department number

As shown in Example 2-1, a unique key constraint exists on the email column of the hr.employees table. The relevant part of the statement is as follows:

CREATE TABLE employees    ( ...
    , email          VARCHAR2(25)
        CONSTRAINT   emp_email_nn NOT NULL ...
    , CONSTRAINT     emp_email_uk UNIQUE (email) ... );

The emp_email_uk constraint ensures that no two employees have the same email address, as shown in the following example:

SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY';

EMPLOYEE_ID LAST_NAME EMAIL
----------- ------------------------- -------------------------
202 Fay PFAY

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
1 VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated

Unless a NOT NULL constraint is also defined, a null always satisfies a unique key constraint. Thus, columns with both unique key constraints and NOT NULL constraints are typical. This combination forces the user to enter values in the unique key and eliminates the possibility that new row data conflicts with existing row data.

Note:

Because of the search mechanism for unique key constraints on multiple columns, you cannot have identical values in the non-null columns of a partially null composite unique key constraint.

Example 5-1 Unique Constraint

SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY';

EMPLOYEE_ID LAST_NAME EMAIL
----------- ------------------------- -------------------------
202 Fay PFAY