第七周翻译

 T-SQL DML 3级阶梯，在SQL server 中执行关系模型

通过 Gregory Larsen， 2017 / 08 / 02 （首次公布：2011/11 / 09）

这一系列

这篇文章是阶梯系列的一部分：T-SQL DML 阶梯

这楼梯将为您提供一个基本的了解如何从SQL Server表的数据，使用SQL Server的Transact-SQL（T-SQL）方言。DML 是数据操作语言，是处理数据语言的一个方面，它包括选择，插入，更新，删除语句。这个阶梯也提供了一些SQL语言的历史。一些关于集合论的一般概念，每个级别都将建立在先前的级别之上，所以有时你完成了，你将会有一个好的理解怎样在SQL Server语言中进行选择和修改。

在这楼梯的前一层，我给你提供了一些关于SELECT的基础语句和SQL的历史。这些级别为理解如何检索数据以及SQL环境是如何演变的提供了基础，因为技术和技术解决方案已经改变了超时工作。在这写级别中我将会探索，怎样执行一个基于关系模型简单的SQL Server数据库。在创建一个数据库之前，首先让我介绍一下关系模型创建者的一些历史。

关系数据建模之父

关系数据库设计的概念首先是由Edgar F. Codd在1970，有一篇题为“大型共享数据库的数据关系模型”的论文，Codd在IBM工作时开发建模理论。IBM没有跳够快在科德的数据建模的概念，因此无法提供关系数据库引擎，开发Codd新关系数据建模理论的第一供应商。Codd的关系模型的概念，现在是用来在SQL Server和其他关系数据库引擎创建关系数据库的框架。

Codd出生在波特兰的英国岛和研究数学和化学在加入英国皇家空军，成为一名二战飞行员。他在1948年移居的纽约，留在IBM工作，在那他是一名数学工程师，他四处漂泊了许多年，最后他移居到加利福尼亚，在IBM圣若泽研究实验室工作，继续工作。Codd完善和证明关系数据模型，直到上世纪90年代，当他每况愈下的健康状况迫使他退休。2003年4月18日Edgar F. Codd去世享年79岁。

在SQL Server中实现关系模型

这个级别不打算教你关于关系数据模型，或者是数据库的设计 。但是将会展示给你怎么样从一个关系模型中创建一个SQL Server数据库。但是在我给你提供一个创建SQL Server数据库的代码块之前，我们首先需要探索一个关系数据模型，它将会被执行。我的简单模型将包含几个实体（数据表），其中包含主键定义和不同实体之间的一些关系（外键约束）。我的简单模型将会是一个酒店预订系统，这个预定系统将需要跟踪客户预订信息。图1说明了这个简单的关系模型，我将使用T-SQL实现：

图1:一个由6个表组成的简单关系数据库模型

通过查看这个模型，您可以看到它包含许多实体(由box表示)来跟踪与预订相关的信息。每个实体由许多属性(列)组成，其中一个或多个属性被标识为主键(粗体和下划线的名称)。同样表示的是实体之间的一些关系(由箭头表示)，它们显示了不同实体之间是如何相互关联的。我将采用实体、属性、主键和关系的模型，然后开发一个表示关系模型设计的物理SQL Server数据库。

要从这个模型构建一个物理数据库，我们需要识别SQL Server中不同的对象，我们将根据这个模型来定义它。对于每个实体或图1中的box，我将在SQL Server中创建一个表。对于每个实体的每个属性，我将在相关联的表中创建一个列。对于每个主键，我将创建一个惟一的集群索引(注意，一个主键也可以使用唯一的非集群索引创建。更多信息索引参考索引在http://www.sqlservercentral.com/stairway/72399/)。最后，对于每一段关系，我将创建一个外键约束。

开始构建我的数据库时，我首先需要创建一个SQL Server数据库来保存我计划创建的所有新数据库对象。我的数据库称为RoomReservation。我将使用下面的t-sql代码来创建我的数据库:

为了从我的模型开始构建RoomReservation数据库对象，我将创建表对象。要在SQL Server中创建一个表，我需要使用create table语句。使用CREATE TABLE语句，我将能够定义每个表和每个表中的所有列。下面是创建SQL Server表的简单语法:

CREATE TABLE语句的完整语法是指在线上的SQL Server Books。

我创建的第一个表将是Customer表，使用清单1中的代码创建。

 

清单3：用主键创建客户表的另一种方法

在这一点上，我已经向您展示了如何创建具有定义主键的表。唯一要展示给您的是如何创建外键约束。但在我能做的，让我先提供在上面的关系数据库模型中创建其他表和主键的脚本。您可以在清单4中找到它。

 

清单4：创建附加表和主键约束

外键约束强制在相互关联的两个表之间执行引用完整性。外键约束定义的表是“引用表”，在表中插入或更新行的任何时刻，都需要在另一个表中有一个相关的记录，称为“引用”表。在图1的关系模型中，这些外键关系用箭头表示。外键约束仅在关系中的一个表上定义。在我的图表中，外键约束将在那些具有箭头尾部（非尖末端）的表上定义。

定义外键约束关系模型在我需要改变每个引用表中添加约束。清单5是T-SQL代码可以用于创建在预订表的外键约束。这个约束确保记录不被插入或更新表中保留不匹配的记录在客户表中发现，基于数字 .

 

清单5：在引用客户表的预订表上创建外键约束

为了完成我的设计，我需要实现其他所有外键约束的图1在我的模型识别。清单6包含 ALTER TABLE语句在我数据模型创建额外的外键约束。

 

一旦我完成了从数据模型构建数据库的工作，接下来就应该验证已完成的设计，以确保它是正确的。这个验证过程是为了保证我在物理数据库中构建的所有数据的完整性规则都得到正确的实现。在我的设计中，我需要验证的规则有：

·插入或更新的所有行都必须有一个特定的值把所有的列定义为非空。

·主键的列不允许有重复的值

·作为外键的列不允许在引用表中匹配不到数据

在验证数据完整性规则之前，我先使用一些用有效数据填充引用的表。我将使用清单7中的代码填充这些表，在这些表中有一些有效的数据:

 

                      

清单7:插入初始数据

为了验证我在数据库中构建的数据完整性规则，我将运行清单8中的INSERT语句。

 

清单8:用INSERT语句测试各种约束

这些INSERT语句中的每一个项目都测试失败，因为它们违反了在room原先设定数据库中构建的数据完整性规则。第一个INSERT语句违反了原先设定的小模快中的非空验证检查。

第二个INSERT语句违反了放在RoomType表上的主键约束。这个INSERT语句试图为RoomTypeID列插入3组值。问题是RoomType表中已经有了一个记录，其中含有有3个房间的值。

最后一个INSERT语句违反了CustomerPaymentType表的外键约束。在这个特定的INSERT语句中，Customer表中没有一个值为2的CustomerID。

为了正确地插入这些记录，需要将插入的数据值进行清理。一旦数据被清理干净，我就可以将这些新数据插入到适当的表中。清单9包含清理的INSERT语句，它将通过所有的数据完整性检查，并成功地插入到room预订数据库中的适当表中

 

清单9:附加的约束测试

关系数据库设计

我的预订示例演示了如何使用关系模型并使用它来实现一个SQL Server数据库。通过使用非空属性、主键和外键，我在数据库设计中构建了数据完整性规则。这使我能够在底层的数据库定义中执行这些规则，而不必在业务处理层中编写代码来验证这些数据规则。通过这样做，允许SQL Server数据库引擎为我执行这些数据来实现数据完整性检查。

通过在关系数据库模型中理解和创建数据库设计，你将实现构建一个健壮且高效的数据库，你也可以将数据完整性检查构建到数据库中。

本文是t-sql DML楼梯阶梯的一部分

注册到我们的RSS订阅源，当我们在楼梯上发布一个新级别的时候，就会得到通知！

 

 

*****翻译选自 Gregory Larsen《Stairway to T-SQL DML Level 3: Implementing a Relational Model in SQL Server》

链接为

http://www.sqlservercentral.com/articles/Stairway+Series/75775/