关系模型的完整性约束

The Relational Model is the most popular of the logic data models, and it’s at the basis of SQL databases.

关系模型是最流行的逻辑数据模型 ，它是SQL数据库的基础。

The Relational Model is based on two simple concepts:

关系模型基于两个简单的概念：

• tables

  桌子

• relations

  关系

The relational model dates back to 1969 and the work of Edgar F. Codd, an English computer scientist. Although as computer programmers we’re used to look with curiosity at new shiny things, a technology that can be central in everything about computers for 50 years is definitely worth studying.

关系模型的历史可以追溯到1969年，是英国计算机科学家Edgar F. Codd的工作。 尽管作为计算机程序员，我们习惯于好奇地研究新事物，但50年来，在计算机的所有方面都处于核心地位的技术绝对值得研究。

The fact that the model is based upon tables makes it very intuitive to use, because we are used to using tables to organize things. Think about an Excel spreadsheet, for example.

该模型基于表的事实使其使用起来非常直观，因为我们习惯于使用表来组织事物。 例如，考虑一个Excel电子表格。

With SQL-based databases, like PostgreSQL, Oracle, MySQL, SQLite and MS SQL Server, and many others, the data analyzed using the ER Model can be modeled using the relational model and be almost immediately transformed into a SQL database format, which can be considered a real-world implementation of the relational model, but we’ll talk about this in other posts.

对于基于SQL的数据库，例如PostgreSQL，Oracle，MySQL，SQLite和MS SQL Server等，可以使用关系模型对使用ER模型分析的数据进行建模，并几乎立即将其转换为SQL数据库格式，从而可以被认为是关系模型的实际实现，但我们将在其他文章中讨论。

In this post I want to talk about the theory and the concepts that the relational model is based upon, not expressed in mathematical terms but what it means in practice.

在这篇文章中，我想谈谈关系模型所基于的理论和概念，不是用数学术语来表达，而是在实践中意味着什么。

If you’re a student, you might find that what I write here is not what is written on your textbook, but maybe you can read it more easily to grasp the more formal concepts expressed in your learning material.

如果您是学生，您可能会发现我在这里写的不是您教科书上写的，但也许您可以更轻松地阅读它，以掌握学习材料中表达的更正式的概念。

桌子 (Tables)

In a relational model, a table is a collection of items.

在关系模型中， 表是项目的集合。

It is organized in rows and columns:

它按行和列进行组织：

Name	Age
Flavio	36
Roger	7
Syd	6

名称	年龄
弗拉维奥	36
罗杰	7
赛德	6

元组 (Tuples)

Each entry in the table is called a tuple. You can also use the terms record or row.

表中的每个条目称为元组 。 您还可以使用术语record或row 。

A tuple represents a row of the table, like this:

元组代表表的一行，如下所示：

Flavio	36

弗拉维奥	36

属性 (Attributes)

An attribute is one single item in the tuple.

属性是元组中的一项。

In this example:

在此示例中：

Flavio	36

弗拉维奥	36

“Flavio” is an attribute. 36 is another attribute.

“ Flavio”是一个属性。 图36是另一个属性。

元组是唯一的 (Tuples are unique)

Every tuple in the table is unique.

表格中的每个元组都是唯一的。

In the relational model, we can’t have duplicate data, meaning every row in the table must be different in at least one attribute.

在关系模型中，我们不能有重复的数据，这意味着表中的每一行在至少一个属性上必须不同。

关系键 (The relation key)

The thing that ensures a tuple is unique is the relation key.

确保元组唯一的是关系键 。

The key is one attribute that must uniquely identify a tuple.

关键字是一个必须唯一标识元组的属性。

If the relation key is a set of attributes, it must be non-redundant. This means that if we remove one of the attributes of the key, the key can’t guarantee its uniqueness.

如果关系键是一组属性，则它必须是非冗余的 。 这意味着，如果我们删除键的属性之一，则键不能保证其唯一性。

If more than one keys can be determined, one of those keys will be identified as the primary key.

如果可以确定一个以上的键，则这些键之一将被标识为主键 。

密钥完整性约束 (Key integrity constraint)

They key attribute(s) of any tuple in the table must never be null, and must never repeat.

它们在表中任何元组的键属性都不能为null ，也不能重复 。

Given a key, we must be able to point to a specific tuple/row without ambiguity.

给定一个密钥，我们必须能够毫无歧义地指向一个特定的元组/行。

域约束 (The domain constraints)

Every attribute has rules about what value it can hold.

每个属性都有关于它可以保持什么值的规则 。

If we decide to store numbers, we can’t store strings, for example. And we might decide to not store strings longer than 10 characters for names.

例如，如果决定存储数字，则不能存储字符串。 并且我们可能决定不存储长度超过10个字符的字符串作为名称。

We can also call this type.

我们也可以称这种类型 。

参照完整性约束 (The referential integrity constraint)

If a table contains reference to a secondary table, or other tuples in the same table, then we must abide to rules that prevent the reference to break.

如果一个表包含对辅助表或同一表中其他元组的引用，则我们必须遵守防止引用中断的规则。

In particular, we must avoid breaking the reference by:

特别是，我们必须避免通过以下方式破坏引用：

• avoiding deleting or editing the primary key of the record that we point to, in the other table.
  避免在另一个表中删除或编辑我们指向的记录的主键。
• avoid inserting a new record with a non-existing key to point to in the other table.
  避免插入一个不存在的键指向另一个表中的新记录。
• avoid changing the key of the record we point to, without ensuring that the new key exists in the other table.
  避免更改我们指向的记录的键，而不必确保新键在另一个表中存在。

A DBMS (Data Base Management System) will implement measures to help us implement referential integrity.

DBMS(数据库管理系统)将采取措施来帮助我们实现参照完整性。

翻译自: https://flaviocopes.com/relational-model/

关系模型的完整性约束

cs