Constraints are part of a database schema definition.
A constraint is usually associated with a table and is created with a CREATE CONSTRAINT or CREATE ASSERTION SQL statement.
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 key or some other unique constraint)
check - an expression is specified, which must evaluate to true for constraint to be satisfied
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 constraints is 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 constraints can 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_constraints view in the AdventureWorks sample database
Rules in 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)
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:
Check ：例如 NOT NULL 。在这里，我们可以指定可以为该特定列输入哪些数据以及该列不需要输入哪些数据。
外键：外键引用其他表的行。因此，一个表中从另一表引用的数据始终可用于引用表。
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:
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
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.
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.
约束可用于强制执行数据的特定属性。一个简单的示例是将 int 列限制为值 [0-100000]。 这个介绍看起来不错。
Constraints can be used to enforce specific properties of data. A simple example is to limit an int column to values [0-100000]. This introduction looks good.
Constraints dictate what values are valid for data in the database. For example, you can enforce the a value is not null (a NOT NULL constraint), or that it exists as a unique constraint in another table (a FOREIGN KEY constraint), or that it's unique within this table (a UNIQUE constraint or perhaps PRIMARY KEY constraint depending on your requirements). More general constraints can be implemented using CHECK constraints.
CHECK 约束（其中NOT NULL 约束是一个变体）。检查同一记录的字段上的表达式是否满足特定条件。这是 Y 轴约束（字段）
FOREIGN KEY 约束。检查是否在另一个表的字段值中找到了某个字段的值。这是 Z 轴约束（表）。
UNIQUE constraint (of which a PRIMARY KEY constraint is a variant). Checks that all values of a given field are unique across the table. This is X-axis constraint (records)
CHECK constraint (of which a NOT NULL constraint 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)
FOREIGN KEY constraint. Checks that a field's value is found among the values of a field in another table. This is Z-axis constraint (tables).
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.
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.
constraints are conditions, that can validate specific condition. Constraints related with database are Domain integrity, Entity integrity, Referential Integrity, User Defined Integrity constraints etc.
发布评论
评论（9）
约束是数据库模式定义的一部分。
约束通常与表关联，并使用
CREATE CONSTRAINT或CREATE ASSERTIONSQL 语句创建。它们定义数据库中的数据必须遵守的某些属性。它们可以应用于一列、整个表、多个表或整个模式。可靠的数据库系统可确保约束始终保持不变（事务内可能除外，即所谓的延迟约束）。
常见的约束类型有：
Constraints are part of a database schema definition.
A constraint is usually associated with a table and is created with a
CREATE CONSTRAINTorCREATE ASSERTIONSQL statement.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:
要理解为什么我们需要约束，您必须首先理解数据完整性的价值。
数据完整性是指数据的有效性。您的数据有效吗？您的数据是否代表了您的设计目的？
您可能会想我问的问题有多么奇怪，但遗憾的是，数据库经常充满垃圾数据、对其他表中行的无效引用，这些数据早已消失......以及对业务逻辑没有任何意义的值您的解决方案不再有效。
所有这些垃圾不仅容易降低您的性能，而且还是您的应用程序逻辑下的定时炸弹，最终会检索它无法理解的数据。
约束是您在设计时创建的规则，可保护您的数据免遭损坏。这对于数据库解决方案的长期生存至关重要。如果没有限制，您的解决方案肯定会随着时间和大量使用而衰退。
您必须承认，设计数据库设计只是解决方案的诞生。此后，它必须（希望）存活很长一段时间，并忍受最终用户（即客户端应用程序）的各种（奇怪）行为。但开发中的设计阶段对于解决方案的长期成功至关重要！尊重它，并给予它所需的时间和注意力。
一位智者曾经说过：“数据必须保护自己！”。这就是约束的作用。这些规则使数据库中的数据尽可能保持有效。
有很多方法可以做到这一点，但基本上可以归结为：
并确保对其他内容的引用
仅当存在时才允许使用表格
实际上存在一个目标行
参考。这也使得
不可能打破这样的
通过删除关系
引用的行创建死链接。
某栏目。您可以创建一个约束，仅允许 VARCHAR 列中出现“黄色”或“蓝色”一词。所有其他值都会产生错误。获取有关使用检查约束的想法 检查 AdventureWorks 示例数据库中的
sys.check_constraints视图你要维护语法
单一位置，并且更容易
将你的约束部署到其他人身上
数据库）
正如我在这里所暗示的，需要进行一些彻底的考虑才能为您的数据库设计构建最佳且最具防御性的约束方法。您首先需要了解上述不同约束类型的可能性和局限性。进一步阅读可能包括：
外键约束 - Microsoft
外键约束 - w3schools
检查约束
祝你好运！ ;)
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:
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.
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 databaseyou to maintain the syntax from a
single place, and making it easier to
deploy your constraints to other
databases)
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
Foreign key constraint - w3schools
CHECK Constraints
Good luck! ;)
约束只不过是数据的规则。可以使用约束来定义哪些数据有效，哪些数据无效。因此，可以保持数据的完整性。
以下是广泛使用的约束：
NOT NULL。在这里，我们可以指定可以为该特定列输入哪些数据以及该列不需要输入哪些数据。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:
NOT NULL. Here we can specify what data we can enter for that particular column and what is not expected for that column.约束可用于强制执行数据的特定属性。一个简单的示例是将 int 列限制为值 [0-100000]。 这个介绍看起来不错。
Constraints can be used to enforce specific properties of data. A simple example is to limit an int column to values [0-100000]. This introduction looks good.
约束规定哪些值对于数据库中的数据有效。例如，您可以强制 a 值不为空（
NOT NULL约束），或者它作为另一个表中的唯一约束存在（FOREIGN KEY约束） ，或者它在此表中是唯一的（UNIQUE约束或PRIMARY KEY约束，具体取决于您的要求）。可以使用CHECK限制。有关 SQL Server 2008 约束的 MSDN 文档可能是您最好的起点。
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 (aFOREIGN KEYconstraint), or that it's unique within this table (aUNIQUEconstraint or perhapsPRIMARY KEYconstraint depending on your requirements). More general constraints can be implemented usingCHECKconstraints.The MSDN documentation for SQL Server 2008 constraints is probably your best starting place.
UNIQUE约束（其中PRIMARY KEY约束是一个变体）。检查给定字段的所有值在表中是否唯一。这是X轴约束（记录）CHECK约束（其中NOT NULL约束是一个变体）。检查同一记录的字段上的表达式是否满足特定条件。这是Y轴约束（字段）FOREIGN KEY约束。检查是否在另一个表的字段值中找到了某个字段的值。这是Z轴约束（表）。UNIQUEconstraint (of which aPRIMARY KEYconstraint is a variant). Checks that all values of a given field are unique across the table. This isX-axis constraint (records)CHECKconstraint (of which aNOT NULLconstraint is a variant). Checks that a certain condition holds for the expression over the fields of the same record. This isY-axis constraint (fields)FOREIGN KEYconstraint. Checks that a field's value is found among the values of a field in another table. This isZ-axis constraint (tables).数据库是概念（或业务）模型的计算机化逻辑表示，由一组非正式业务规则组成。这些规则是用户理解的数据含义。由于计算机仅理解形式表示，因此业务规则不能直接在数据库中表示。它们必须映射到一个正式的表示，一个逻辑模型，它由一组完整性约束组成。这些约束（数据库模式）是业务规则在数据库中的逻辑表示，因此也是 DBMS 理解的数据含义。因此，如果 DBMS 不知道和/或不强制执行代表业务规则的全套约束，则它对数据含义的理解不完整，因此无法通过防止损坏来保证 (a) 其完整性， (b) 从中得出的推论的完整性（即查询结果）——这是 DBMS 充其量是不完整的另一种说法。
注意：DBMS“理解”的含义（完整性约束）与用户理解的含义（业务规则）并不相同，但是，尽管失去了一些含义，我们还是获得了从数据中机械化逻辑推理的能力。
费边·帕斯卡的《一类古老的错误》
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.
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.
"An Old Class of Errors" by Fabian Pascal
SQL 中基本上有 4 种类型的主要约束：
域约束：如果为新的属性值提供了一个属性值，
元组不属于指定的属性域
键约束：如果新元组中键属性的值
关系中的另一个元组已存在
引用完整性：如果新元组中的外键值
引用的主键值在引用中不存在
关系
实体完整性：如果新元组中的主键值为 null
There are basically 4 types of main constraints in SQL:
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
约束是条件，可以验证特定条件。
与数据库相关的约束有域完整性、实体完整性、引用完整性、用户定义完整性约束等。
constraints are conditions, that can validate specific condition.
Constraints related with database are Domain integrity, Entity integrity, Referential Integrity, User Defined Integrity constraints etc.