原文链接：http://www.sqlservercentral.com/articles/Stairway+Series/75775/
通过阶梯到T-SQLDML等级3：在SQL Server中实现关系模型
GregoryLarsen，2017/08/02(第一次出版:2011 /11/09)
此系列
本文是此阶梯系列的一部分：到T-SQL DML的阶梯
这个阶梯将会给你提供一个关于如何使用SQL Server的transact - SQL(t- SQL)方言来对SQL Server表中的数据进行处理的基本了解。DML是数据操纵和数据处理方面的语言。它包括SELECT，INSERT，UPDATE和DELETE语句。这个阶梯还将提供一些SQL语言的历史和关于集合理论的一般概念。每个级别都将建立在先前的级别上，因此，当你完成的时候你将对从SQL Server中如何选择和修改数据方面有更好的理解。
在这个阶梯之前的那一级，我提供了有关基本SELECT语句和SQL历史的信息。那些级别给你一个基本的理解如何检索数据的基础和SQL环境如何随着技术和技术解决方案发生的改变而改变。在这个级别，我将探索如何实现基于关系模型的简单SQL Server数据库。在建库之前，首先允许我介绍一下关系模型的创建者的一些历史。
关系数据模型之父
关系数据库设计的概念由Edgar F. Codd于1970年在题为“大型共享数据库数据的关系模型”的论文中首次提出。Codd在IBM工作时开发了这种建模理论。IBM在Codd的数据建模概念上跳得不够快，因此没有成为第一个提供利用Codd新关系数据建模理论的关系数据库引擎的供应商。Codd的关系建模成为现在用于在SQL Server和其他关系数据库引擎中创建关系数据库的框架。
Codd出生于英国的波特兰岛，在加入英国皇家空军之前学习数学和化学，并成为二战的一名飞行员。他在1948年搬到纽约并在IBM工作，在那他是一名数学程序员。他辗转多年终于最终在加利佛尼亚落脚，并在IBM的San Jose研究实验室工作。Codd继续为改善和证明关系数据模型的合理性，直到20世纪90年代，不佳的健康状况迫使他退休。Edgar F. Codd 于2003年4月18日去世，享年79岁。
在SQLServer中实现关系模型
这个阶梯不是来教你关系数据建模，或数据库设计，而是仅仅教你如何从一个关系模型创建一个SQL Server数据库。但是，在我能够给你提供创建SQL Server数据库的代码块之前，我们首先要探索将要实现的关系数据模型。我的简单模型将包含一些具有主键定义的实体（数据表）和不同实体之间的一些关系（外键约束）。
我简单的关系模式是一个简单的酒店预订系统。该预订系统将需要跟踪客户预约信息。图1说明了我将使用T-SQL实现的简单关系模型：
图1：一个简单的由6个表组成的关系数据库模型。
通过看这个模型，您可以看到它包含许多实体(由方框表示)来跟踪预订相关信息。每个实体由一些属性（列）组成，其中一个或多个属性被标识为主键（粗体和带下划线的名称）。同时也表示了实体（由箭头表示）之间的一些关系，其中显示了不同的实体之间是如何相互关联的。我将使用这种模型的实体，属性，主键和关系，然后开发一个表示该关系模型设计的物理SQL Server数据库。
要从此模型构建物理数据库，我们需要根据此模型识别SQL Server中将要定义的不同对象。对于图1中的每个实体或框，我将在SQL Server 创建一个表。对于每个实体的每个属性，我将在关联的表中创建一个列。对于每一个主键，我将创建一个独一无二的聚簇索引（使用唯一的非聚簇索引也可以创建主键。有关索引的更多信息，请参阅索引阶梯http://www.sqlservercentral.com/ st 通过看这个模型，您可以看到它包含许多实体(由方框表示)来跟踪预订相关信息。每个实体由一些属性（列）组成，其中一个或多个属性被标识为主键（粗体和带下划线的名称）。同时也表示了实体（由箭头表示）之间的一些关系，其中显示了不同的实体之间是如何相互关联的。我将使用这种模型的实体，属性，主键和关系，然后开发一个表示该关系模型设计的物理SQL Server数据库。
要从此模型构建物理数据库，我们需要根据此模型识别SQL Server中将要定义的不同对象。对于图1中的每个实体或框，我将在SQL Server 创建一个表。对于每个实体的每个属性，我将在关联的表中创建一个列。对于每一个主键，我将创建一个 airway/72399 /）。最后，对于每个关系，我将创建一个外键约束。
为了开始构建数据库，我首先需要创建一个SQL Server数据库来保存我计划创建的所有新的数据库对象。我的数据库将叫RoomReservation。
我将用以下的 T-SQL 代码来创建我的数据库:
CREATE DATABASE RoomReservation;
为了从我的模型创建我的RoomReservation数据库对象，我将接着创建表对象。为了在SQL Server创建表，我需要CREATE TABLE语句。使用CREATE TABLE语句，我将能定义每个表和每个表里的所有列。以下是创建SQL Server表的简单语法：
CREATE TABLE <table_name> (
<column_definition> [,…N]);
地址：
<table_name> = Name of table
<column_definition> = column_name data_type,[NULL | NOT NULL]
对于CREATE TABLE语句的完整语法，请参见联机的SQL Server Books。
我创建的第一个表是Customer表，它是利用列表1中的代码创建的。
USE RoomReservation;
GO
CREATE TABLE dbo.Customer (
CustomerIdINT NOT NULL,
FirstNameNVARCHAR(50) NOT NULL,
LastNameNVARCHAR(50) NOT NULL,
Address1NVARCHAR(100) NOT NULL,
Address2NVARCHAR(100) NULL,
PhoneNumberNVARCHAR(22) NOT NULL,
EmailAddressVARCHAR(100) NULL,
CityVARCHAR(100) NOT NULL,
StateProvinceNVARCHAR(100) NOT NULL,
PostalCodeNVARCHAR(100) NOT NULL);
列表1：创建Customer表
在这段代码中，当我创建Customer表时我创建了我需要的所有列。另一方面，我也指定了当这个表中插入或更新记录时，此列是否需要一个值。我通过指定一些列NOT NULL（不为空值），而其他列指定为NULL（空值）。
如果一个列被定义为NOT NULL（不为空值），那意味着你不能创建一个记录，除非你用一个实际值填充此列。而使用NULL（空值）规范定义列表意味着您可以创建一行而不指定此列的值，或者另一种方法是列允许NULL（空值）。在我上面的CREATE TABLE语句中，我允许了列Address2和列EmailAddress支持NULL（空值），而剩余列需要在创建行时提供一个值。
这条CREATE TABLE语句并不能完全定义我Customer表，正如上面展示的关系数据库模型。我仍需要创建一个主键来约束列CustomerID。这个主键约束将确保在这张表中没有两条记录有相同的CustomerID值。创建这个主键的代码在列表2中。
USE RoomReservation;
GO
ALTER TABLE dbo.Customer ADD CONSTRAINT
PK_CustomerPRIMARY KEY CLUSTERED (CustomerId);
列表2：添加一个PRIMARY KEY（主键）来约束Customer表
此条ALTER TABLE语句给我的Customer表添加了一个主键约束。该主键将以名为PK_Customer的聚簇索引的形式创建。
在Transact-SQL语言中，通常可以有不止一种方法来做相同的事情。或者，我可以通过运行列表3中的CREATE TABLE语句一次来创建我的Customer表和主键。
USE RoomReservation;
GO
CREATE TABLE dbo.Customer (
CustomerIdINT NOT NULL CONSTRAINT PK_Customer PRIMARY KEY,
FirstNameNVARCHAR(50) NOT NULL,
LastNameNVARCHAR(50) NOT NULL,
Address1NVARCHAR(100) NOT NULL,
Address2NVARCHAR(100) NULL,
PhoneNumberNVARCHAR(22) NOT NULL,
EmailAddressNVARCHAR(100) NULL,
CityVARCHAR(100) NOT NULL,
StateProvinceNVARCHAR(100) NOT NULL,
PostalCodeNVARCHAR(100) NOT NULL);
列表3：用 PRIMARY KEY（主键）创建Customer表的其他的方法
在这一方面，我已经展示了如何用一个已经定义的PRIMARY KEY（主键）创建一张表。剩下要展示给你的是如何创建一FOREIGN KEY（外键）约束。但在我能够这样做之前请允许我先提供给你在上面的关系数据库模型中创建的其余的表和PRIMARY KEYS（主键）的脚本。你可以在列表4中找到它。
USE RoomReservation;
GO
CREATE TABLE dbo.Reservation (
ReservationIdINT NOT NULL,
ArrivalDateDATETIME NOT NULL,
DepartureDateDATETIME NOT NULL,
DailyRateSMALLMONEY NOT NULL,
ReservationStatusIDINT NOT NULL,
CustomerIdINT NOT NULL,
RoomTypeIDINT NOT NULL);
ALTER TABLE dbo.Reservation ADD CONSTRAINT
PK_ReservationPRIMARY KEY CLUSTERED (ReservationId);
CREATE TABLE dbo.RoomType (
RoomTypeId INTNOT NULL,
RoomDescNVARCHAR(1000) NOT NULL);
ALTER TABLE dbo.RoomType ADD CONSTRAINT
PK_RoomTypePRIMARY KEY CLUSTERED (RoomTypeId);
CREATE TABLE dbo.ReservationStatus (
ReservationStatusIdINT NOT NULL,
ReservationStatusDescNVARCHAR(50) NOT NULL);
ALTER TABLE dbo.ReservationStatus ADD CONSTRAINT
PK_ReservationStatus PRIMARY KEY CLUSTERED (ReservationStatusId);
CREATE TABLE dbo.PaymentType (
PaymentTypeIdINT NOT NULL,
PaymentTypeDescNVARCHAR(50) NOT NULL);
ALTER TABLE dbo.PaymentType ADD CONSTRAINT
PK_PaymentTypePRIMARY KEY CLUSTERED (PaymentTypeId);
CREATE TABLE dbo.CustomerPaymentType (
PaymentTypeIdINT NOT NULL,
CustomerIdINT NOT NULL,
PaymentNotesNVARCHAR(2000) NULL);
ALTER TABLE dbo.CustomerPaymentType ADD CONSTRAINT
PK_CustomerPaymentType PRIMARY KEY CLUSTERED (PaymentTypeId,CustomerId);
列表4：创建附加的表和PRIMARY KEY（主键）约束
一个FOREIGN KEY（外键）约束在两张彼此相互关联的两个表之间执行参照完整性。被定义外键约束的表是“引用表”，同时，作为“引用”表，在任何时候在表中的一行中插入或更新时，也要求此表在另外一张表的记录上有关联。在图1的关系模型中，这些外键关系是用箭头表示的。FOREIGN KEY（外键）约束仅仅被定义在关系中的其中一张表里。在我的图中FOREIGN KEY（外键）约束将只被定义在那些附加了箭头尾端（非尖端）的那些表上。
为了在我的关系模型中定义这些FOREIGN KEY（外键）我将需要更改每个引用表来添加约束。列表5是我能够用T-SQL代码在Reservation表中创建一个FOREIGN KEY（外键）约束。此约束确保在Reservation表中不会插入或更新记录，除非在CustomerId的基础上在Customer表中查询到相应的记录。
USE RoomReservation;
GO
ALTER TABLE dbo.Reservation
ADD CONSTRAINT FK_Reservation_CustomerPaymentType FOREIGNKEY (CustomerId)
REFERENCESdbo.Customer (CustomerID);
列表5：在引用Customer表的Reservation表上创建FOREIGN KEY（外键）约束
为了完成我的设计我需要在我图一的模型中实施所有其他的外键约束。列表6包含了在我的数据模型里创建附加外键约束的ALTER TABLE语句。
USE RoomReservation;
GO
ALTER TABLE dbo.Reservation
ADD CONSTRAINT FK_Reservation_RoomType FOREIGN KEY(RoomTypeId)
REFERENCESdbo.RoomType (RoomTypeId);
ALTER TABLE dbo.Reservation
ADD CONSTRAINT FK_Reservation_ReservationStatus FOREIGNKEY (ReservationStatusId)
REFERENCESdbo.ReservationStatus (ReservationStatusId);
ALTER TABLE dbo.CustomerPaymentType
ADD CONSTRAINT FK_CustomerPaymentType_PaymentType FOREIGNKEY (PaymentTypeId)
REFERENCESdbo.PaymentType (PaymentTypeId);
ALTER TABLE dbo.CustomerPaymentType
ADD CONSTRAINT FK_CustomerPaymentType_Customer FOREIGNKEY (CustomerId)
REFERENCESdbo.Customer (CustomerId);
列表6：创建附加FOREIGN KEY（外键）约束
验证数据库设计
一旦我从一个数据模型中完成建立一个数据库我就应该检验我所实现的设计以确保它是准确无误的。这个验证过程是为了确保我在物理数据库中构建的所有数据完整性规则都正确地实现了。这些数据是我需要在我的设计里检验的。
♦ 所有插入的或者更新的行必须有一个被特殊定义的值给任意一列定义为NOTNULL。
♦ PRIMARYKEY的列不允许出现重复的值
♦有外键约束的列不允许拥有在关联表中没有匹配记录的数据。
在我检查数据完整性准则之前我首先需要用一些有效的数据来填充关联表。我将用列表7中的代码来用有效的数据来填写那些表：
USE RoomReservation;
GO
SET NOCOUNT ON;
-- Create PaymentType records
INSERT INTO PaymentType VALUES(1,'Visa');
INSERT INTO PaymentType VALUES(2,'MasterCard');
INSERT INTO PaymentType VALUES(3,'American Express');
-- Create Customer
INSERT INTO Customer VALUES
(1,'Greg','Larsen','123Some Place'
,NULL,'123-456-7890',Null,'MyCity','MA','12345');
-- Create Reservation Status
INSERT INTO ReservationStatus VALUES (1,'Booked');
INSERT INTO ReservationStatus VALUES (2,'Cancelled');
-- Create Room Type
INSERT INTO RoomType VALUES (1,'Kingsize');
INSERT INTO RoomType VALUES (2,'Queen');
INSERT INTO RoomType VALUES (3,'Double');
列表7：插入初始数据
为了验证我建立的数据库的数据完整性，我将运行在列表8中的INSERT 代码。
USE RoomReservation;
GO
-- Violates NOT NULL constraint
INSERT INTO Reservation VALUES(1,'2011-8-1 5:00 PM'
,'2011-8-2 9:00AM'
,150.99,NULL,1,1);
-- Violates Primary Key Constraint
INSERT INTO RoomType VALUES (3,'Suite');
-- Violates Foreign Key Constraint
INSERT INTO CustomerPaymentType VALUES (1,2,'Will need aninternet connection');
这些INSERT声明语句中的任意一个都应该是不成立的，因为它们都违反了在已经建立的RoomReservation数据库中的数据完整性规则。第一个INSERT声明违反了ReservationStatusID列的NOT NULL的有效性检查。
第二个INSERT声明语句违反了被放置在RoomType表中的PRIMARY KEY 约束。这条INSERT声明语句试图在RoomTypeID列中插入一条值为3数据。这个问题是在RoomTypeID值为3的RoomType表这里已经有了一条记录。
最后一条INSERT声明语句违反了CustomerPaymentType表的FOREIGN KEY 约束。在这条特殊的INSERT声明语句里，Customer表里没有值为2的 CustomerID。
为了准确的插入这些记录，这些被插入的数据需要被清空。一旦数据被清空，我将能够在合适的表里插入新的数据。列表9包含了被清空的而且将通过数据完整性检查好成功插入到RoomReservation 数据库里合适的表中INSERT声明语句。
USE RoomReservation;
GO
-- Violates NOT NULL constraint
INSERT INTO Reservation VALUES(1,'2011-8-1 5:00 PM'
,'2011-8-2 9:00AM'
,150.99,1,1,1);
-- Violates Primary Key Constraint
INSERT INTO RoomType VALUES (4,'Suite');
-- Violates Foreign Key Constraint
INSERT INTO CustomerPaymentType VALUES (1,1,'Will need aninternet connection');
列表9：附加约束检查
关系数据库设计
我的预订示例演示了如何使用关系模型并使用它来实现SQL Server数据库。
通过使用NOT NULL，PRIMARY KEY约束，和 FOREIGN KEY 约束, 我把建立数据完整性规则运用到我的数据库设计上。这允许我使用这些规则进行基本数据定义，而不是在我的业务处理层中为了检验这些数据规则而敲代码。通过做这些，我可以允许SQL Server数据库引擎为我执行这些数据完整性的检查。
通过理解并围绕关系数据模型创建你的数据库，你将能创建一个稳定高效的数据库实现，在那里你可以将数据完整性检查直接放入数据库中。
本文是 Stairway to T-SQL DML进阶的一部分