SQL 什么是数据库约束？

What are database constraints?

提问by bala3569

What is a clear definition of database constraint? Why are constraints important for a database? What are the types of constraints?

什么是数据库约束的明确定义？为什么约束对数据库很重要？约束的类型有哪些？

回答by Ziga Kranjec

Constraints are part of a database schema definition.

约束是数据库模式定义的一部分。

A constraint is usually associated with a table and is created with a CREATE CONSTRAINTor CREATE ASSERTIONSQL statement.

约束通常与表相关联，并使用SQL 语句CREATE CONSTRAINT或CREATE ASSERTIONSQL 语句创建。

They define certain properties that data in a database must comply with. They can apply to a column, a whole table, more than one table or an entire schema. A reliable database system ensures that constraints hold at all times (except possibly inside a transaction, for so called deferred constraints).

它们定义了数据库中的数据必须遵守的某些属性。它们可以应用于一列、整个表、多个表或整个模式。可靠的数据库系统可确保约束始终有效（可能在事务内部除外，即所谓的延迟约束）。

Common kinds of constraints are:

常见的约束类型有：

• not null- each value in a column must not be NULL
• unique- value(s) in specified column(s) must be unique for each row in a table
• primary key- value(s) in specified column(s) must be unique for each row in a table and not be NULL; normally each table in a database should have a primary key - it is used to identify individual records
• foreign key- value(s) in specified column(s) must reference an existing record in another table (via it's primary keyor some other unique constraint)
• check- an expression is specified, which must evaluate to true for constraint to be satisfied

• not null- 列中的每个值都不能为NULL
• unique- 指定列中的值对于表中的每一行必须是唯一的
• 主键- 指定列中的值对于表中的每一行必须是唯一的，并且不能为NULL；通常数据库中的每个表都应该有一个主键 - 它用于标识单个记录
• 外键- 指定列中的值必须引用另一个表中的现有记录（通过它的主键或其他一些唯一约束）
• check- 指定了一个表达式，它必须评估为真才能满足约束

回答by Alex

To understand why we need constraints, you must first understand the value of data integrity.

要了解我们为什么需要约束，您必须首先了解数据完整性的价值。

Data Integrity refers to the validity of data. Are your data valid? Are your data representing what you have designed them to?

数据完整性是指数据的有效性。你的数据有效吗？您的数据是否代表了您设计它们的目的？

What weird questions I ask you might think, but sadly enough all too often, databases are filled with garbage data, invalid references to rows in other tables, that are long gone... and values that doesn't mean anything to the business logic of your solution any longer.

你可能会想我问什么奇怪的问题，但遗憾的是，数据库经常充斥着垃圾数据，对其他表中行的无效引用，这些早已不复存在......以及对业务逻辑没有任何意义的值您的解决方案不再。

All this garbage is not alone prone to reduce your performance, but is also a time-bomb under your application logic that eventually will retreive data that it is not designed to understand.

所有这些垃圾不仅容易降低您的性能，而且还是您的应用程序逻辑下的定时炸弹，最终将检索它无法理解的数据。

Constraints are rules you create at design-time that protect your data from becoming corrupt. It is essential for the long time survival of your heart child of a database solution. Without constraints your solution will definitely decay with time and heavy usage.

约束是您在设计时创建的规则，用于保护您的数据不被损坏。它对于数据库解决方案的长期生存至关重要。没有限制，您的解决方案肯定会随着时间的推移和大量使用而衰减。

You have to acknowledge that designing your database design is only the birth of your solution. Here after it must live for (hopefully) a long time, and endure all kinds of (strange) behaviour by its end-users (ie. client applications). But this design-phase in development is crucial for the long-time success of your solution! Respect it, and pay it the time and attention it requires.

您必须承认，设计您的数据库设计只是您的解决方案的诞生。此后，它必须（希望）存活很长时间，并忍受其最终用户（即客户端应用程序）的各种（奇怪）行为。但是这个开发的设计阶段对于您的解决方案的长期成功至关重要！尊重它，并给予它所需的时间和注意力。

A wise man once said: "Data must protect itself!". And this is what constraints do. It is rules that keep the data in your database as valid as possible.

一位智者曾经说过：“数据必须自我保护！” . 这就是约束的作用。这是使数据库中的数据尽可能有效的规则。

There are many ways of doing this, but basically they boil down to:

有很多方法可以做到这一点，但基本上归结为：

• Foreign key constraintsis probably the most used constraint, and ensures that references to other tables are only allowed if there actually exists a target row to reference. This also makes it impossible to break such a relationship by deleting the referenced row creating a dead link.
• Check constraintscan ensure that only specific values are allowed in certain column. You could create a constraint only allowing the word 'Yellow' or 'Blue' in a VARCHAR column. All other values would yield an error. Get ideas for usage of check constraints check the sys.check_constraintsview in the AdventureWorks sample database
• Rulesin SQL Server are just reusable Check Constraints (allows you to maintain the syntax from a single place, and making it easier to deploy your constraints to other databases)

• 外键约束可能是最常用的约束，并确保仅当确实存在要引用的目标行时才允许引用其他表。这也使得无法通过删除创建死链接的引用行来破坏这种关系。
• 检查约束可以确保特定列中只允许特定值。您可以创建一个约束，只允许在 VARCHAR 列中使用“黄色”或“蓝色”一词。所有其他值都会产生错误。获取有关使用检查约束的想法检查sys.check_constraintsAdventureWorks 示例数据库中的视图
• SQL Server 中的规则只是可重用的检查约束（允许您从一个地方维护语法，并更容易将您的约束部署到其他数据库）

As I've hinted here, it takes some thorough considerations to construct the best and most defensive constraint approach for your database design. You first need to know the possibilities and limitations of the different constraint types above. Further reading could include:

正如我在这里暗示的那样，为您的数据库设计构建最佳和最具防御性的约束方法需要一些彻底的考虑。您首先需要了解上述不同约束类型的可能性和局限性。进一步阅读可能包括：

FOREIGN KEY Constraints - Microsoft

外键约束 - Microsoft

Foreign key constraint - w3schools

外键约束 - w3schools

CHECK Constraints

检查约束

Good luck! ;)

祝你好运！;)

回答by user3107247

Constraints are nothing but the rules on the data. What data is valid and what is invalid can be defined using constraints. So, that integrity of data can be maintained. Following are the widely used constraints:

约束只不过是对数据的规则。可以使用约束来定义哪些数据有效，哪些数据无效。因此，可以保持数据的完整性。以下是广泛使用的约束：

1. Primary Key: which uniquely identifies the data . If this constraint has been specified for certain column then we can't enter duplicate data in that column
2. Check: Such as NOT NULL. Here we can specify what data we can enter for that particular column and what is not expected for that column.
3. Foreign key: Foreign key references to the row of other table. So that data referred in one table from another table is always available for the referencing table.

1. 主键：唯一标识数据。如果已为特定列指定了此约束，则我们不能在该列中输入重复数据
2. 检查：如NOT NULL。在这里，我们可以指定我们可以为该特定列输入哪些数据以及该列不需要哪些数据。
3. 外键：对其他表行的外键引用。因此，从另一个表中引用的一个表中的数据始终可用于引用表。

回答by Anders Abel

Constraints can be used to enforce specific properties of data. A simple example is to limit an int column to values [0-100000]. Thisintroduction looks good.

约束可用于强制执行数据的特定属性。一个简单的示例是将 int 列限制为值 [0-100000]。这个介绍看起来不错。

回答by Jon Skeet

Constraints dictate what values are valid for data in the database. For example, you can enforce the a value is not null (a NOT NULLconstraint), or that it exists as a unique constraint in another table (a FOREIGN KEYconstraint), or that it's unique within this table (a UNIQUEconstraint or perhaps PRIMARY KEYconstraint depending on your requirements). More general constraints can be implemented using CHECKconstraints.

约束规定了哪些值对数据库中的数据有效。例如，您可以强制 a 值不为空（NOT NULL约束），或者它作为另一个表中的唯一约束存在（FOREIGN KEY约束），或者它在该表中是唯一的（UNIQUE约束或PRIMARY KEY约束，取决于您的要求）。可以使用CHECK约束来实现更一般的约束。

The MSDN documentation for SQL Server 2008 constraintsis probably your best starting place.

SQL Server 2008 约束的MSDN 文档可能是您最好的起点。

回答by Quassnoi

1. UNIQUEconstraint (of which a PRIMARY KEYconstraint is a variant). Checks that all values of a given field are unique across the table. This is X-axis constraint (records)

2. CHECKconstraint (of which a NOT NULLconstraint is a variant). Checks that a certain condition holds for the expression over the fields of the same record. This is Y-axis constraint (fields)

3. FOREIGN KEYconstraint. Checks that a field's value is found among the values of a field in another table. This is Z-axis constraint (tables).

1. UNIQUE约束（其中一个PRIMARY KEY约束是一个变体）。检查给定字段的所有值在表中是否唯一。这是X-axis 约束（记录）

2. CHECK约束（其中一个NOT NULL约束是一个变体）。检查特定条件是否适用于同一记录的字段上的表达式。这是Y-axis 约束（字段）

3. FOREIGN KEY约束。检查是否在另一个表的字段值中找到了一个字段的值。这是Z-axis 约束（表）。

回答by onedaywhen

A database is the computerized logical representation of a conceptual (or business) model, consisting of a set of informal business rules. These rules are the user-understood meaning of the data. Because computers comprehend only formal representations, business rules cannot be represented directly in a database. They must be mapped to a formal representation, a logical model, which consists of a set of integrity constraints. These constraints — the database schema — are the logical representation in the database of the business rules and, therefore, are the DBMS-understood meaning of the data. It follows that if the DBMS is unaware of and/or does not enforce the full set of constraints representing the business rules, it has an incomplete understanding of what the data means and, therefore, cannot guarantee (a) its integrity by preventing corruption, (b) the integrity of inferences it makes from it (that is, query results) — this is another way of saying that the DBMS is, at best, incomplete.

数据库是概念（或业务）模型的计算机化逻辑表示，由一组非正式业务规则组成。这些规则是用户理解的数据含义。由于计算机仅理解形式表示，因此不能直接在数据库中表示业务规则。它们必须映射到一个正式的表示，一个由一组完整性约束组成的逻辑模型。这些约束（数据库模式）是业务规则在数据库中的逻辑表示，因此是 DBMS 理解的数据含义。因此，如果 DBMS 不知道和/或不强制执行表示业务规则的完整约束集，它就无法完全理解数据的含义，因此，

Note: The DBMS-“understood” meaning — integrity constraints — is not identical to the user-understood meaning — business rules — but, the loss of some meaning notwithstanding, we gain the ability to mechanize logical inferences from the data.

注意：DBMS——“理解”的意思——完整性约束——与用户理解的意思——业务规则——不同，但是，尽管失去了一些意义，我们仍然能够从数据中机械化逻辑推理。

"An Old Class of Errors" by Fabian Pascal

Fabian Pascal 的“老错误”

回答by Ragu

There are basically 4 types of main constraints in SQL:

SQL 中基本上有 4 种类型的主要约束：

• Domain Constraint:if one of the attribute values provided for a new tuple is not of the specified attribute domain

• Key Constraint:if the value of a key attribute in a new tuple already exists in another tuple in the relation

• Referential Integrity:if a foreign key value in a new tuple references a primary key value that does not exist in the referenced relation

• Entity Integrity:if the primary key value is null in a new tuple

• 域约束：如果为新元组提供的属性值之一不属于指定的属性域

• 键约束：如果新元组中的键属性值已经存在于关系中的另一个元组中

• 引用完整性：如果新元组中的外键值引用了被引用关系中不存在的主键值

• 实体完整性：如果新元组中的主键值为空

回答by Prashant Kumbharkar

constraints are conditions, that can validate specific condition. Constraints related with database are Domain integrity, Entity integrity, Referential Integrity, User Defined Integrity constraints etc.

约束是条件，可以验证特定条件。与数据库相关的约束有域完整性、实体完整性、引用完整性、用户定义完整性约束等。