翻译Stairway to T-SQL DML Level 3: Implementing a Relational Model in SQL Server
通往T-SQL DML Level 3的阶梯：在SQL Server中实现关系模型
来自 GregoryLarsen, 2017/08/02 (第一次出版: 2011/11/09)
系列
这篇文章是阶梯系列的一部分:通往T-SQL DML的阶梯。
通过使用SQL Server的Transact-SQL (T-SQL)语言，这个阶梯将为您提供如何使用SQL Server表的数据的基本理解。DML是数据操纵语言，是处理数据的语言的一方面。它包括语句的选择、插入、更新和删除。这个阶梯将提供一些SQL语言的历史和一些关于集合论的一般概念。每个级别都将建立在之前的级别之上，所以当你完成时，你将对如何从SQL Server中选择和修改数据有一个很好的理解。
在这个阶梯的前一级别，我向你提供了关于基本选择语句和SQL历史的信息。这些级别阶段为你提供了如何检索数据以及SQL环境如何随着技术和技术解决方案的变化而变化的理解基础。在这个级别上，我将探讨如何实现基于关系模型的简单SQL Server数据库。在开始创建数据库之前，首先让我介绍一下关系模型的创建者的一些历史。
关系数据库之父
关系数据库设计的概念最初是由Edgar F. Codd在1970年提出的，论文标题为“大型共享数据银行的关系数据模型”。Codd在IBM工作时构想出了这个建模理论。IBM在Codd的数据建模概念上思维跳跃的速度不够快，因此并不是第一个利用了Codd的新关系数据建模理论提供关系数据库引擎的供应商。Codd的关系建模概念现在是用于在SQL Server和其他关系数据库引擎中创建关系数据库的框架。
Codd出生在英国的波特兰岛，在二战中加入英国皇家空军成为飞行员之前，他学习了数学和化学。1948年他搬到纽约，开始在IBM工作，在那当一名数学程序员。他漂泊了好几年，最终搬到加利福尼亚，在IBM公司位于加州圣何塞的研究中心工作。Codd一直致力于完善和证明关系数据模型，直到上世纪90年代他的健康问题迫使他退休。Edgar F. Codd于2003年4月18日去世，享年79岁。
在SQL Server中实现关系模型
这个阶梯不是用来教你关系数据建模或数据库设计的，而是教你如何从一个关系模型中创建一个SQL Server数据库。但是，在我为你提供创建SQL Server数据库的代码块之前，我们首先需要探索将要被执行的关系数据模型。我的简单模型将包含几个实体(数据表)，其中有主键定义和不同实体之间的一些关系(外键约束)。
我的简单关系模型是一个简单的酒店预订系统，这个预订系统需要跟踪客户预订信息。图1表明我将使用T-SQL语言实现这个简单的关系模型：
图1:一个包含6个表的简单的关系数据库模型
通过回顾这个模型，你可以看到它包含了许多实体(以方框表示)来跟踪客户预订的相关信息。每个实体由许多属性(列)组成，其中一个或多个属性被标识为主键(粗体和下划线的名称)。同样表示实体之间的一些关系(以箭头表示)，以显示不同的实体之间是如何相互关联的。我将使用实体、属性、主键和关系的模型，然后开发一个表示此关系模型的设计的物理SQL Server数据库。
为了从这个模型中构建一个物理数据库，我们需要根据此模型识别SQL Server中将要定义的不同对象。对于图1中的每个实体或框，我将在SQL Server中创建一个表；对于每个实体的每个属性，我将在关联的表中创建一个列；对于每个主键，我将会创建一个唯一的聚簇索引(注意:主键也可以使用唯一的非聚簇索引创建。有关索引的更多信息，请参见http://www.sqlservercentral.com/stairway/72399/）；最后，对于每个关系，我将创建一个外键约束。
为了开始构建我的数据库，首先我需要创建一个SQL Server数据库来保存我计划创建的所有新数据库对象。我的数据库将被称为RoomReservation。我将使用以下的T-SQL代码创建我的数据库:
要从我的模型中开始构建我的RoomReservation数据库对象，我将创建表对象。要在SQL Server中创建表，我需要使用CREATE TABLE语句。使用CREATE TABLE语句，我将能够定义每个表和每个表中的所有列。下面是创建SQL Server表的简单语法:
Where:
= Name of table
= column_name data_type,[NULL | NOT NULL]
CREATE TABLE 语句的完整语法是指在线预订的SQL Server 。
我创建的第一个表是Coustomer，使用列表1中的代码创建。
清单1:创建Customer表
在这个代码中，当我创建Customer表时，我创建了我所需的所有列，但我还指定了在插入或更新记录时，该列是否需要一个值。我通过在某些列上指定NOT NULL实现了这一点，而其他列则指定为NULL。
如果列被定义为NOT NULL，这意味着除非用实际值填充该列，否则无法创建记录。尽管使用NULL规范定义列意味着你可以创建一行，而无需为该列指定值，或者说，另一种方法是该列允许空值。在我的CREATE TABLE语句中，我允许列Address 2和EmailAddress支持NULL，而所有其余的列都需要在创建行时提供一个值。这个CREATE TABLE语句没有完全定义我的Customer表，因为它在我的关系数据库模型中表示。我仍然需要在列CustomerID上创建一个主键约束。此主键约束将确保本表中没有两个记录具有相同的CustomerID值。创建主键的代码如清单2所示。
清单2：向Customer表添加主键约束
此ALTER TABLE语句向我的Customer表添加了一个主键约束。该主键将以名为PK_Customer的聚集索引的形式创建。
在Transact-SQL语言中，通常有超过一种方法来做同样的事情。或者，通过运行清单3中的CREATE TABLE语句，可以一次性创建Customer表和主键。
清单3：用主键创建Customer表的另一种方法
现在我已经向您展示了如何创建一个具有定义了主键的表。剩下的唯一要向您展示的是如何创建外键约束。但在我这样做之前，首先让我提供脚本，以便在我的关系数据库模型中创建其余的表和主键。您可以在清单4中找到它。
清单4：创建附加表和主键约束
外键约束强制两个相互关联的表之间的引用完整性。外键约束定义的表是“引用表”，并要求在表中插入或更新行时，在另一个表中有一个相关记录，称为“Reference”表。在图1所示我的关系模型中，这些外键关系由箭头表示。外键约束仅在关系中的一个表上定义。在我的图表中，外键约束将定义在那些带有箭头尾(非尖头末端)的表上。
要在我的关系模型中定义这些外键约束，我需要修改每个引用表以添加约束。清单5是我可以用来在Reservation表上创建外键约束的T-SQL代码。此约束确保记录不会被插入或更新到Reservation表中，除非在CustomerId的Customer表中找到匹配的记录。
清单5：在引用Customer表的Reservation表上创建外键约束
为了完成我的设计，我需要实现图1中我的模型中标识的所有其他外键约束。清单6包含ALTER TABLE语句，用于在我的数据模型中创建额外的外键约束。
清单6：创建额外的外键约束
验证数据库设计
一旦我完成了从数据模型构建数据库，我应该验证实现的设计，以确保它是正确的。这个验证过程是为了确保我在物理数据库中构建的所有数据完整性规则都得到了正确的实现。在我的设计中，这里是我需要验证的规则。
插入或更新的所有行必须为任何定义为NOT NULL的列定义一个特定值。
作为主键的列不允许重复值。
具有外键约束的列不允许在引用表中没有匹配记录的数据。
我在验证数据完整性规则之前，首先需要使用一些有效数据填充引用的表。我将使用清单7中的代码来填充这些表中的一些有效数据：
清单7：插入初始数据
为了验证我在数据库中构建的数据完整性规则，我将运行清单8中的INSERT语句。
清单8：使用INSERT语句测试各种约束
这些INSERT语句中的每个语句都应该失败，因为它们违反了内置到RoomReserve数据库中的数据完整性规则。第一个INSERT语句违反了ReservationStatusID列的NOT NULL验证检查。
第二个INSERT语句违反了放置在RoomType表上的主键约束。此INSERT语句试图为RoomTypeID列插入3的值。问题是RoomType表中已经有一个记录，其RoomTypeID值为3。
最后一个INSERT语句违反CustomerPaymentType表的外键约束。在这个特定的INSERT语句中，Customer表中没有值为2的CustomerID。
要正确插入这些记录，需要清除插入的数据值。清理完数据后，我将能够将这些新数据插入到适当的表中。清单9包含清理后的INSERT语句，这些语句将通过所有数据完整性检查，并成功地插入到RoomReservation数据库中的适当表中：
清单9：附加约束测试
关系数据库设计
我的预订示例演示了如何采用关系模型并使用它实现SQL Server数据库。通过使用NOT NULL、主键和外键约束，我在数据库设计中直接构建了数据完整性规则。这允许我在底层数据库定义中强制执行这些规则，而不必在业务处理层中编写代码来验证这些数据规则。通过这样做，我允许SQL Server数据库引擎为我执行这些数据完整性检查。
通过了解和创建围绕关系数据库模型的数据库设计，您将构建一个坚定和高效的数据库实现，在这里您可以将数据完整性检查直接构建到数据库中。
本文是Stairway to T-SQL DML阶梯的一部分。