CREATE DATABASE RoomReservation

2023-02-06 07:22:22

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

CREATE TABLE <table_name> (
<column_definition> [,…N]);

哪里：
<table_name> =表的名称
<column_definition> = column_name data_type，[NULL | NOT NULL]
有关CREATE TABLE语句的完整语法，请参阅SQL Server联机丛书。
我创建的第一个表将是使用清单1中的代码创建的Customer表。

USE RoomReservation;
GO
CREATE TABLE dbo.Customer (
CustomerId INT NOT NULL,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
Address1 NVARCHAR(100) NOT NULL,
Address2 NVARCHAR(100) NULL,
PhoneNumber NVARCHAR(22) NOT NULL,
EmailAddress VARCHAR(100) NULL,
City VARCHAR(100) NOT NULL,
StateProvince NVARCHAR(100) NOT NULL,
PostalCode NVARCHAR(100) NOT NULL);

清单1：创建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_Customer PRIMARY 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 (
CustomerId INT NOT NULL CONSTRAINT PK_Customer PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
Address1 NVARCHAR(100) NOT NULL,
Address2 NVARCHAR(100) NULL,
PhoneNumber NVARCHAR(22) NOT NULL,
EmailAddress NVARCHAR(100) NULL,
City VARCHAR(100) NOT NULL,
StateProvince NVARCHAR(100) NOT NULL,
PostalCode NVARCHAR(100) NOT NULL);

清单3：使用PRIMARY KEY创建Customer表的另一种方法
在这一点上，我已经向您展示了如何使用定义的PRIMARY KEY创建表。唯一可以告诉你的是如何创建FOREIGN KEY约束。但在我可以这样做之前，让我首先提供您在上面的关系数据库模型中创建其余表和PRIMARY KEYS的脚本。你可以在清单4中找到它。

USE RoomReservation;
GO

CREATE TABLE dbo.Reservation (
ReservationId INT NOT NULL,
ArrivalDate DATETIME NOT NULL,
DepartureDate DATETIME NOT NULL,
DailyRate SMALLMONEY NOT NULL,
ReservationStatusID INT NOT NULL,
CustomerId INT NOT NULL,
RoomTypeID INT NOT NULL);

ALTER TABLE dbo.Reservation ADD CONSTRAINT
PK_Reservation PRIMARY KEY CLUSTERED (ReservationId);

CREATE TABLE dbo.RoomType (
RoomTypeId INT NOT NULL,
RoomDesc NVARCHAR(1000) NOT NULL);

ALTER TABLE dbo.RoomType ADD CONSTRAINT
PK_RoomType PRIMARY KEY CLUSTERED (RoomTypeId);

CREATE TABLE dbo.ReservationStatus (
ReservationStatusId INT NOT NULL,
ReservationStatusDesc NVARCHAR(50) NOT NULL);

ALTER TABLE dbo.ReservationStatus ADD CONSTRAINT
PK_ReservationStatus PRIMARY KEY CLUSTERED (ReservationStatusId);

CREATE TABLE dbo.PaymentType (
PaymentTypeId INT NOT NULL,
PaymentTypeDesc NVARCHAR(50) NOT NULL);

ALTER TABLE dbo.PaymentType ADD CONSTRAINT
PK_PaymentType PRIMARY KEY CLUSTERED (PaymentTypeId);

CREATE TABLE dbo.CustomerPaymentType (
PaymentTypeId INT NOT NULL,
CustomerId INT NOT NULL,
PaymentNotes NVARCHAR(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是可用于在“预留”表上创建FOREIGN KEY约束的T-SQL代码。此约束确保在“预留”表中不会插入或更新记录，除非在Customer表中基于CustomerId找到匹配的记录。

USE RoomReservation;
GO
ALTER TABLE dbo.Reservation
ADD CONSTRAINT FK_Reservation_CustomerPaymentType FOREIGN KEY (CustomerId)
REFERENCES dbo.Customer (CustomerID);

清单5：在引用Customer表的Reservation表上创建FOREIGN KEY约束
为了完成我的设计，我需要实现在图1中我的模型中识别的所有其他外键constings。清单6包含在我的数据模型中创建附加外键约束的ALTER TABLE语句。

USE RoomReservation;
GO
ALTER TABLE dbo.Reservation
ADD CONSTRAINT FK_Reservation_RoomType FOREIGN KEY (RoomTypeId)
    REFERENCES dbo.RoomType (RoomTypeId);

ALTER TABLE dbo.Reservation
ADD CONSTRAINT FK_Reservation_ReservationStatus FOREIGN KEY (ReservationStatusId)
    REFERENCES dbo.ReservationStatus (ReservationStatusId);

ALTER TABLE dbo.CustomerPaymentType
ADD CONSTRAINT FK_CustomerPaymentType_PaymentType FOREIGN KEY (PaymentTypeId)
    REFERENCES dbo.PaymentType (PaymentTypeId);

ALTER TABLE dbo.CustomerPaymentType
ADD CONSTRAINT FK_CustomerPaymentType_Customer FOREIGN KEY (CustomerId)
    REFERENCES dbo.Customer (CustomerId);

清单6：创建附加的FOREIGN KEY约束
验证数据库设计
一旦我完成了从数据模型构建数据库，我应该验证实现的设计，以确保它是正确的。此验证过程是确保我内置到我的物理数据库中的所有数据完整性规则正确实施。在我的设计中，我需要验证的规则
插入或更新的所有行必须为定义为NOT NULL的任何列定义特定的值。
PRIMARY KEY的列不允许重复的值
具有外键常量的列不允许在引用的表中没有匹配记录的数据
在我可以验证数据完整性规则之前，我首先需要使用一些有效的数据填充引用的表。我将使用清单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','123 Some 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:00 AM'
                                , 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 an internet connection');

清单8：使用INSERT语句测试各种约束
这些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包含清理的INSERT语句，它将传递所有数据完整性检查，并成功插入到RoomReservation数据库中的相应表中：

USE RoomReservation;
GO
-- Violates NOT NULL constraint
INSERT INTO Reservation VALUES(1,'2011-8-1 5:00 PM'
                                ,'2011-8-2 9:00 AM'
                                , 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 an internet connection');

单9：附加约束测试
关系数据库设计
我的预订示例演示了如何使用关系模型并使用它来实现SQL Server数据库。通过使用NOT NULL，PRIMARY KEY和FOREIGN KEY constaints，我将数据完整性规则直接构建到我的数据库设计中。这允许我在底层数据库定义中强制执行这些规则，而不必在我的业务处理层中编写代码来验证这些数据规则。通过这样做，我允许SQL Server数据库引擎为我执行这些数据完整性检查。
通过围绕关系数据库模型了解和创建数据库设计，您将构建一个强大而高效的数据库实现，您可以在其中构建数据库中的数据完整性检查。

码农公寓

相关文章