数据库设计阶梯3：建筑表 这些条款通常具有可选（0或更多）或强制性（1个或更多）成员资格。 一对一（1：1）的关系是一个实体A的最多一个实例与实体B的一个实例相关联。例如，采取传统的丈夫和妻子之间的关系。每个丈夫只有一个老婆;每个妻子只有一个丈夫。在这个例子中都是强制性的。 一对多（1：n）关系是对于实体A的一个实例，实体B有零个，一个或多个实例，但对于实体B的一个实例，实体A只有一个实例。例

                                                                                                                   数据库设计阶梯3：建筑表
                                                                                                    作者：Joe Celko，2013/09/18（首次发表：2010/05/25）

该系列
这篇文章是楼梯系列的一部分：楼梯数据库设计

新设计和创建数据库的任务？作为对SQL的所有作家的最广泛阅读的Joe Celko解释了基础知识。像往常一样，他偶然惊喜甚至是最经验丰富的数据库专业人士。乔连续四年获得DBMS杂志读者选择奖。他在美国，英国，北欧国家，南美洲和非洲都教过SQL。他在ANSI / ISO SQL标准委员会任职十年，为SQL-89和SQL-92标准做出了贡献。

有几种类型的表，每种都有对规则和完整性约束的特殊要求。无论需求如何，表级别的约束将确保执行规则并维护数据完整性。

在第一级中，我们为数据元素命名了它们并对它们进行了分类。在二级中，我们使用SQL中的数据类型和约束对数据元素建模，以给出行。在三级中，我们将这些行放入表中。一张桌子不仅仅是一堆以一个名字收集在一起的行。

一个列在表中只能出现一次。这只是有道理的如果你记录了一个人的鞋子大小两次，那么在最坏的情况下柱子不同意的话，最好是多余的。现在我们可以在每行的列之间有表级别的CHECK约束。它们与我们以前使用的CHECK限制没有太大的不同。它们可以被命名，并将出现在CREATE TABLE语句中的列声明列表中，而不附加到任何行。 。例如：

CONSTRAINT Valid_Employee_Age--在出生之前不要雇用人
 CHECK（emp_birth_date <emp_hire_date）
不要将约束合并成一个巨大的CHECK（）子句通常是个好主意。错误消息将包含约束名称，因此单独的约束将使您更好地了解一个名为“Bad_Things_Happened”约束的单个怪物出错的情况。

继续我们对冗余的仇恨，在表级，我​​们希望每一行都是独一无二的，原因相同。这可以通过表约束完成。两个表级限制是UNIQUE和PRIMARY KEY，它们都是单列和多列。

UNIQUE约束表示列中的列或组合在表中是唯一的。但是如果在一个或多个列中有NULL，我们将允许它像一个唯一的值一样。 PRIMARY KEY声明与其中的所有列的NOT NULL和UNIQUE具有相同的效果。但是由于历史原因，一个表只能有一个PRIMARY KEY声明。这些列用作表之间的其他约束的默认值，但不要担心现在。

如何使用唯一性约束取决于所涉及的表的类型。一般来说，我们可以将表格分为三种：

1,‘实体
2’关系
3附加
实体表是由列建模的属性定义的同一类的一组事物。每行都是这种事情的一个实例。每行都有相同的列。如果你能看到它的感觉，看到或触摸它，那么它是一个实体。实体表的名称不应该是单数的（除非真的只有这个集合的一个成员），因为它建立一个集合。这个名字需要是复数形式，如果可能的话，需要集体。例如“员工”不好，“员工”较好，“人事”最好。 “树”不好，“树”更好，“森林”最好。你可以添加自己的例子。

实体也被分类为弱或强。一个强大的实体存在着自己的优点，而一个弱实体存在，因为一个或多个强大的实体。您需要购买才能享受折扣。

关系表引用一个或多个实体并建立它们之间的关系。除了引用实体之外，关系还可以具有自己的属性。婚姻执照号码属于婚姻，而不是丈夫，妻子或部长。

关系的程度是关系中的实体数量。二元关系有两个实体，我们在现实世界中喜欢它们，因为它们很简单。递归二进制关系将实体与自身相关联。一般的n-ary关系涉及n个实体，例如与买方，卖方和贷方的房屋抵押。将n-ary关系分解为二元关系并不总是可能的。关系中的成员资格可以是可选的或强制性的。可选的会员资格意味着我们可以拥有一种零个实体 - 购买并不总是得到折扣。

关系的基数是两个实体中每个实体的相关事件的实际数量。关系的基本连接类型是：一对一，一对多，多对多。这些条款通常具有可选（0或更多）或强制性（1个或更多）成员资格。

一对一（1：1）的关系是一个实体A的最多一个实例与实体B的一个实例相关联。例如，采取传统的丈夫和妻子之间的关系。每个丈夫只有一个老婆;每个妻子只有一个丈夫。在这个例子中都是强制性的。

一对多（1：n）关系是对于实体A的一个实例，实体B有零个，一个或多个实例，但对于实体B的一个实例，实体A只有一个实例。例如，一个部门有很多员工;每个员工被分配到一个部门。根据您的业务规则，您可能会允许未分配的员工或空的部门。

有时称为非特定的多对多（m：n）关系是对于实体A的一个实例，实体B有零个，一个或多个实例，并且对于实体B的一个实例，零，一个或多个实体A.一个例子可能是比萨饼和客户。

辅助表既不是实体也不是关系;它提供了信息。它们是用于替换SQL中的计算的日历或其他查找表。他们经常被误解，被视为实体或关系表。

让我们更具体一些。销售订单是客户（实体）和我们的库存（实体）之间的关系。订单详细信息是存在的弱实体，因为我们有订单。该关系具有不是库存或客户的一部分的订单号。运输成本从辅助表获得。下面是这个例子的一些骨架表。我正在为客户使用GTIN（全球贸易商品编号）和DUNS（数据通用编号系统）。设计数据库时，始终寻找行业标准。

CREATE TABLE Sales_Orders
（order_nbr INTEGER NOT NULL PRIMARY KEY
 CHECK（order_nbr> 0），
 customer_duns CHAR（9）NOT NULL，
 order_shipping_amt DECIMAL（5,2）NOT NULL
 CHECK（shipping_amt> = 0.00），
 等等）;
CREATE TABLE Sales_Order_Details
（order_nbr INTEGER NOT NULL，
 gtin CHAR（15）NOT NULL，
 PRIMARY KEY（order_nbr，gtin），
 item_qty INTEGER NOT NULL
 CHECK（item_qty> 0），
 item_unit_price DECIMAL（8,2）NOT NULL
 CHECK（item_unit_price> = 0.00））;
CREATE TABLE客户
（customer_duns CHAR（9）NOT NULL PRIMARY KEY
 CHECK（customer_duns LIKE'[0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] “），
 等等）;
创建表库存
（gtin CHAR（15）NOT NULL PRIMARY KEY
 CHECK（gtin LIKE'[0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9]“），
 onhand_qty INTEGER NOT NULL
 CHECK（onhand_qty> = 0），
我们可以看到销售订单是客户和库存之间的关系。订单有自己的密钥（order_nbr），但没有任何东西强制我们仅使用有效的客户DUNS号码或产品GTIN代码，我们实际上在库存中。实际上，我可以把Order DUNS和GTIN的代码插入到Orders表中，现在就是这样宣告的。

这是REFERENCES子句所在。它是什么让我们从数据模型中强制执行所有的基数和度数。引用不是链接或指针。那些是物理概念，参考是一个逻辑概念，我们不知道它是如何实现的。它执行的是引用表列与引用表中的单个行匹配的规则。这意味着引用表中的行必须是唯一的;默认情况下，引用表中的PRIMARY KEY是目标，但它不必是。引用表中的值称为外键 - 它们不是表中的键，而是模式中的其他位置。

以下是具有更多肉体的骨架模式：

CREATE TABLE Sales_Orders
（order_nbr INTEGER NOT NULL PRIMARY KEY
 CHECK（order_nbr> 0），
 customer_duns CHAR（9）NOT NULL
 参考客户（customer_duns），
 order_shipping_amt DECIMAL（5,2）DEFAULT 0.00 NOT NULL
 CHECK（shipping_amt> = 0.00），
 等等）;
CREATE TABLE Sales_Order_Details
（order_nbr INTEGER NOT NULL
 参考订单（order_nbr），
 gtin CHAR（15）NOT NULL
 参考库存（gtin），
 PRIMARY KEY（order_nbr，gtin）， - 两列键
 item_qty INTEGER NOT NULL
 CHECK（item_qty> 0），
 item_unit_price DECIMAL（8,2）NOT NULL
 CHECK（item_unit_price> = 0.00））;
CREATE TABLE客户
（customer_duns CHAR（9）NOT NULL PRIMARY KEY
 CHECK（customer_duns LIKE'[0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] “），
 等等）;
创建表库存
（gtin CHAR（15）NOT NULL PRIMARY KEY
 CHECK（gtin LIKE'[0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9]“），
 onhand_qty INTEGER NOT NULL
 CHECK（onhand_qty> = 0），
 等等）;

请注意，我们只需要在DUNS和GTIN是键的地方使用CHECK（）约束，而不是它们在引用表中出现的位置。引用实体表，客户和库存;关系表，订单，引用其他表。这是一般的模式，但并不具体。

这个子句的多列形式如下所示：

FOREIGN KEY（order_nbr，gtin）
参考Sales_Order_Details（order_nbr，gtin）
FOREIGN KEY子条款中的列在引用表中必须与引用的键匹配，列为列，但可能有不同的名称。我可以通过在正确的地方放置唯一性约束来获得1：1，1：n和n：m关系。作为腋窝表的一个例子，我们可以根据订单的总价值计算运输成本。桌子可能看起来像这样：

CREATE TABLE Shipping_Costs
（start_order_amt_tot DECIMAL（10,2）NOT NULL，
 end_order_amt_tot DECIMAL（10,2）NOT NULL，
CONSTRAINT Valid_Shipping_Range
 CHECK（start_order_amt_tot <end_order_amt_tot），
PRIMARY KEY（start_order_amt_tot，end_order_amt_tot），
 shipping_amt DECIMAL（5,2）NOT NULL
 CHECK（shipping_amt> 0.00））;
虽然我们在辅助运输费用表上声明了一个主键，但它不像实体的键 - 没有验证或验证，它不是标识符。要使用此表，我们将使用以下内容查询：

SELECT shipping_amt
  来自Shipping_Costs
 WHERE <order amount total> BETWEEN start_order_amt_tot AND end_order_amt_tot;
作为一个练习，尝试编写一个约束，以防止起始和结束范围重叠和间隙。如果需要，可以重新设计桌子。

在修订的骨架模式中，当您尝试对不在库存中的产品进行订单时，您将收到一条错误，表示“实际上是”缺货！“，您可以尝试其他操作。但是，如果您尝试从库存中删除某个产品，您还会收到一条错误，表示有效，“嘿，有人订购了这个垃圾”，所以你必须去每个订单，用其他的东西替换这个项目或使其为空（如果允许），然后才能从库存中删除它。

这是使用声明参照完整性（DRI）动作的地方。语法是：

ON DELETE [NO ACTION | SET DEFAULT | SET NULL |级联]
ON UPDATE [NO ACTION | SET DEFAULT | SET NULL |级联]
删除和更新称为“数据库事件”;当它们发生在桌面上时，就会发生DRI动作。

NO ACTION =事务被回滚并且您收到消息。当你只有一个简单的REFERENCES子句时，这是默认值。
SET DEFAULT =引用的列由事件更改，但引用列将更改为其默认值。显然，引用列需要在其上声明默认值。这些默认值必须在引用的表中。
SET NULL =引用的列由事件更改，但引用列更改为NULL。显然，引用列需要为NULL。这就是NULL的“益处”。
CASCADE =引用的列被事件改变，并且这些相同的值被级联到引用列。这是实践中最重要的选择。例如，如果我们要停止产品，我们可以从库存中删除它，而ON DELETE CASCADE会使SQL引擎自动删除Sales_Order_Details中的匹配行。同样，如果您更新库存中的一个项目，ON UPDATE CASCADE将会自动将旧值替换为引用的新值。
执行任何这些操作后，引用完整性约束仍然有效。这是最后的骨架：

CREATE TABLE Sales_Orders
（order_nbr INTEGER NOT NULL PRIMARY KEY
 CHECK（order_nbr> 0），
 customer_duns CHAR（9）NOT NULL
 参考客户（customer_duns）
 ON UPDATE CASCADE
 ON DELETE CASCADE，
 order_shipping_amt DECIMAL（5,2）DEFAULT 0.00 NOT NULL
 CHECK（shipping_amt> = 0.00），
 等等）;
CREATE TABLE Sales_Order_Details
（order_nbr INTEGER NOT NULL
 参考订单（order_nbr）
 ON UPDATE CASCADE
 ON DELETE CASCADE，
 gtin CHAR（15）NOT NULL
 参考库存（gtin）
 ON UPDATE CASCADE
 ON DELETE CASCADE，
 PRIMARY KEY（order_nbr，gtin）， - 两列键
 item_qty INTEGER NOT NULL
 CHECK（item_qty> 0），
 item_unit_price DECIMAL（8,2）NOT NULL
 CHECK（item_unit_price> = 0.00））;
看看你能否弄清楚：

客户死亡，我们删除他。
我们把草坪Gnome雕像变成更有品味的粉红色火烈鸟。
我们停止粉红色的火烈鸟。
有人尝试在步骤1到3之后订购草坪Gnome
显然，我正在放弃补货问题和其他事情，但我们会得到那些。

该系列
阅读楼梯数据库设计系列的其余部分，并查看其他文章。

 
这篇文章是楼梯数据库设计楼梯的一部分。

注册我们的RSS Feed，一旦我们在楼梯里发布一个新的级别，就会收到通知！注册我们的RSS Feed，一旦我们在楼梯里发布一个新的级别，就会收到通知！