你的位置:首页 > 数据库

[数据库]数据库设计(3/9):创建表


对于设计和创建数据库完全是个新手?没关系,Joe Celko, 世界上读者数量最多的SQL作者之一,会告诉你这些基础。和往常一样,即使是最专业的数据库老手,也会给他们带来惊喜。Joe是DMBS杂志是多年来最受 读者喜爱的作者。他在美国、英国,北欧,南美及非洲传授SQL知识。他在ANSI / ISO SQL标准委员会工作了10年,为SQL-89和SQL-92标准做出了杰出贡献。


有很多表类型,每个都有它们特定规则和完整性约束的需求。不管什么需求,表层级的约束会确保那些规则被执行,数据完整性被保持。

 在第一篇,我们为了它们是什么,更好的区分它们来命名数据元。在第二篇,我们用SQL里的数据类型和约束来模型化数据元,来提供我们行。在第三篇,我们将把这些行放入表。表是在一个名称下,很多行的集合。

在表里,列只会出现一次。这样做是有道理的;如果你两次记录某人的鞋子大小,这将是多余的,当列不一致时是混淆的。现在我们可以有表层级的在每行的列里的检查(CHECK)约束。这和之前列上的(CHECK)检查并没有啥区别。它们可以在CREATE TABLE语句里,多个列声明里命名并出现,不附加到任何行。例如:

1 CONSTRAINT Valid_Employee_Age-- don't hire people before they are born2 CHECK (emp_birth_date < emp_hire_date) 

通常不应该把检查组合成一个大的CHECK()子句。错误信息会包含约束名称,因此独立的约束会,相比单个复杂命名的约束,给让你更清楚的发现问题。

继续我们的冗余问题,在表层级我们想每个行因同个原因而唯一。这可以通过约束实现。两个表层级的约束是UNIQUE和PRIMARY KEY,它们可以是单列或多列组合。

UNIQUE约束表示在表里,列或列的组合是唯一的。但在列或多个列中有NULL,如果它是唯一值,我们还是允许的。PRIMARY KEY声明,与对于表里面的所有列,NOT NULL且UNIQUE有同样的效果。但由于历史原因,表只能有一个PRIMARY KEY声明。这些列用来作为表之间的其他约束,但现在不要担心这个。

唯一性约束如何使用取决于涉及的表类型。一般来说,我们可以把表分为三类:

  1. 实体(Entity)
  2. 关系(Relationship)
  3. 辅助(Auxiliary)

实体表是多个同类事物,通过列的模型属性定义。每一行是这类东西的实例。每行有同样的列。如果你可以看到它感觉,看到它或感受它,那它是一个实体。实体表的命名不应该是单数(除非这个集合里真的只有一个成员),因为它模型化了一组。命名应该是复数,可能的话,使用集合命名。例如,“Employee”不好,“Employees”更好,“Personnel”最好。“Tree”不好,“Trees”更好,“Forest”最好。你可以添加你自己的例子。

实体也区分弱和强。强实体存在有它自己的优点,同时,弱实体存在因为一个或多个强实体。你需要购买前,你可以有个折扣。

关系表指的是一个或多个实体表,并且它们之间建立关系。关系可以有它自己委外引用实体的属性。结婚登记号属于婚姻,不属于丈夫,妻子或牧师。

关系级别是关系里实体的个数。二元关系有2个实体,在现实世界中我们喜欢它们,因为它们简单。二元迭代关系关联到实体本身。一般的n元关系涉及n个实体,就像有买家,卖家和银行的房贷。通常不能把n元关系分解为二元关系。成员的关系可以是可选或必须的。可选的关系表示我们可以有一类的0实体——并不是所有的买卖都有折扣。

关系基数是对于每2个实体,相关出现的实际数量。关系的基本连接类型有:1:1,1:n,和n:n。这些术语通常是符合可选(0或更多)或必须的(1或更多)的关系。

1:1关系是一个实体A的最多一个实例与实体B的一个实例关联的时候。例如,拿通常的丈夫和妻子的关系。每个丈夫有且只要一个妻子;每个妻子有且只有一个丈夫。在这个例子都是必须一个的。

1:n关系是实体A的一个实例,对于实体B的一个实例有0个,一个或多个实体B的实例,实体A的实例只有一个的时候。一个例子会是一个部门有很多员工;每个员工分配到一个部门。取决于你的业务规则,你会允许未分配部门的员工或空的部门。

n:n关系,有时称作非特定的,对于实体A的一个实例,有0个,一个或多个实体B的实例,并且对于实体B的一个实例,有0个,一个或多个实体A的实例。这样的例子可以是披萨和客户。

辅助表不是实体也不是关系;它提供信息。它们是像日历或在SQL里替换计算的查询表(look up tables)。它们经常被误解被当实体或关系表对待。

我们来具体说下。销售订单是客户(实体)和我们的库存(实体)之间的关系。订单明细是存在的弱实体,因为我们有订单。这个关系有一个不是库存或客户一部分的订单号。运费从辅助表获得。对于这个例子,这里我用了一些骨架表。对于订单项目,我使用GTIN(Global Trade Item Number),对于客户,我使用GUNS(Data Universal Numbering System)。在你设计数据库的时候,记得都先看看行业标准。

 1 CREATE TABLE Sales_Orders 2  3 (order_nbr INTEGER NOT NULL PRIMARY KEY 4  5 CHECK (order_nbr > 0), 6  7 customer_duns CHAR(9) NOT NULL, 8  9 order_shipping_amt DECIMAL (5,2) NOT NULL10 11 CHECK (shipping_amt >= 0.00),12 13  etc);14 15 CREATE TABLE Sales_Order_Details16 17 (order_nbr INTEGER NOT NULL,18 19 gtin CHAR(15) NOT NULL,20 21 PRIMARY KEY (order_nbr, gtin),22 23 item_qty INTEGER NOT NULL24 25 CHECK (item_qty > 0),26 27 item_unit_price DECIMAL (8,2) NOT NULL28 29 CHECK (item_unit_price >=0.00));30 31 CREATE TABLE Customers32 33 (customer_duns CHAR(9) NOT NULL PRIMARY KEY34 35 CHECK (customer_duns LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),36 37  etc);38 39 CREATE TABLE Inventory40 41 (gtin CHAR(15) NOT NULL PRIMARY KEY42 43 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]'),44 45 onhand_qty INTEGER NOT NULL46 47 CHECK (onhand_qty >= 0),

我们可以看到订单表是客户和库存间的关系。订单有它们自己的主键(order_nbr),但没有东西强制我们使用有效的客户DUNS号或对于我们库存里的产品GTIN号。事实上,我可以插入显然无效的DUNS和GTIN码到订单表,现在就这样声明。

这就是我们要引入REFERENCES子句的地方。它是让我们从数据模型强制所有基数和程度的东西。引用(reference)不是个链接或指针。这些是物理概念,引用是个逻辑概念,我们不知道它如何实现。它强制的是,在引用表里,引用表列符合单行的规则。这意味着在引用表里的行必须唯一;默认情况下,在引用表里可以使用主键(PRIMARY KEY),但不必这样。在引用表的值可以称为外键(Foreign Keys)——它们不在它们的表里,但在架构里的其它地方。

这是上面有更多信息的主要架构:

 1 CREATE TABLE Sales_Orders 2 (order_nbr INTEGER NOT NULL PRIMARY KEY 3 CHECK (order_nbr > 0), 4 customer_duns CHAR(9) NOT NULL 5 REFERENCES Customers(customer_duns), 6 order_shipping_amt DECIMAL (5,2) DEFAULT 0.00 NOT NULL 7 CHECK (shipping_amt >= 0.00), 8  etc); 9 10 CREATE TABLE Sales_Order_Details11 (order_nbr INTEGER NOT NULL12 REFERENCES Orders(order_nbr),13 gtin CHAR(15) NOT NULL14 REFERENCES Inventory(gtin),15 PRIMARY KEY (order_nbr, gtin),-- two column key16 item_qty INTEGER NOT NULL17 CHECK (item_qty > 0),18 item_unit_price DECIMAL (8,2) NOT NULL19 CHECK (item_unit_price >= 0.00));20 21 CREATE TABLE Customers22 (customer_duns CHAR(9) NOT NULL PRIMARY KEY23 CHECK (customer_duns LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),24  etc);25 26 CREATE TABLE Inventory27 (gtin CHAR(15) NOT NULL PRIMARY KEY28 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]'),29 onhand_qty INTEGER NOT NULL30 CHECK (onhand_qty >= 0),31 etc); 

注意,在DUNS和GTIN是主键的地方,我们只有CHECK()约束,不是在它们出现的引用表里。实体表,客户和库存是引用的;关系表,订单,引用了其它表。这是常用模式,但这个设置不是固定的。

这个子句的多列看起来像这样:

1 FOREIGN KEY (order_nbr, gtin)2 REFERENCES Sales_Order_Details(order_nbr, gtin) 

在FOREIGN KEY子句的列是引用表里需要匹配的引用主键,列对列,但会有不同的名称。我可以通过在相应的地方放置唯一约束来设置1:1,1:n和n:n的关系。作为辅助表的一个例子,我们可以基于订单总额计算运费。表看起来像这样:

1 CREATE TABLE Shipping_Costs2 (start_order_amt_tot DECIMAL (10,2) NOT NULL,3 end_order_amt_tot DECIMAL (10,2) NOT NULL,4 CONSTRAINT Valid_Shipping_Range5 CHECK (start_order_amt_tot < end_order_amt_tot),6 PRIMARY KEY (start_order_amt_tot, end_order_amt_tot),7 shipping_amt DECIMAL (5,2) NOT NULL8 CHECK (shipping_amt > 0.00));

 当我们在辅助运费表上声明了主键(PRIMARY KEY),对于实体,它不想主键——没有验证或核查,它不是个标识。使用这个表,我们可以这样查询:

1 SELECT shipping_amt2  FROM Shipping_Costs3 WHERE <order amount total> BETWEEN start_order_amt_tot AND end_order_amt_tot;

作为练习,尝试写下会从重复和断层上阻止开始和结束范围的约束。如果你需要的话,可以重新设计表。

在修正后的主要架构里,当你下没有库存的订单,你会收到错误提示“没有库存!”,这样的话,你可以试下别的。但如果你尝试从库存里删除产品,你同样也会收到错误提示“额,有人已经下了此产品的订单”,因此在可以从库存里删它之前,你必须到每个订单用别的值或NULL值来替换它。

这里就是引用完整性(Declarative Referential Integrity (DRI))用的地方。语法是:

1 ON DELETE [NO ACTION | SET DEFAULT | SET NULL | CASCADE]2 ON UPDATE [NO ACTION | SET DEFAULT | SET NULL | CASCADE]

删除和更新是所谓的“数据基础事件(data base events)”;当它们发生到表时,就会发生DRI操作。

  1. NO ACTION:事务回滚,你收到提示。当有简单的REFERENCES子句时的默认操作。
  2. SET DEFAULT:引用的列通过事件改变,但引用的列值会修改为它们的默认值。当然,引用的列在它们上面要有声明的默认值。这些默认需要在引用表里。
  3. SET NULL:引用的列通过事件改变,但引用的列值会修改为NULL。当然,引用的列允许NULL值。这是引入NULL值“无罪推定(benefit of the doubt)”的地方。
  4. CASCADE:引用的列通过事件改变,这些值会级联到引用的列。实际中这是最重要的选项。例如,如果我们想停止一个产品,我们可以从库存里删除它,ON DELETE CASCADE会让SQL引擎会在Sales_Order_Details自动删除匹配的行。同样,如果在库存里更新一个项目,ON UPDATE CASCADE会用新值自动替换引用的列。

在这些操作完成后,引用完整性约束还是有效的。这是最终的架构:

 1 CREATE TABLE Sales_Orders 2 (order_nbr INTEGER NOT NULL PRIMARY KEY 3 CHECK (order_nbr > 0), 4 customer_duns CHAR(9) NOT NULL 5 REFERENCES Customers(customer_duns) 6 ON UPDATE CASCADE 7 ON DELETE CASCADE, 8 order_shipping_amt DECIMAL (5,2) DEFAULT 0.00 NOT NULL 9 CHECK (shipping_amt >= 0.00),10  etc);11 12 CREATE TABLE Sales_Order_Details13 (order_nbr INTEGER NOT NULL14 REFERENCES Orders(order_nbr)15 ON UPDATE CASCADE16 ON DELETE CASCADE,17 gtin CHAR(15) NOT NULL18 REFERENCES Inventory(gtin)19 ON UPDATE CASCADE20 ON DELETE CASCADE,21 PRIMARY KEY (order_nbr, gtin),-- two column key22 item_qty INTEGER NOT NULL23 CHECK (item_qty > 0),24 item_unit_price DECIMAL (8,2) NOT NULL25 CHECK (item_unit_price >= 0.00)); 

看看下面的情况发生时,你会找出会发生什么?

  1. 一个客户走了,我们删除它。
  2. 我们修改Lawn Gnome雕像为更有品味的Pink Flamingo。
  3. 我们停止销售Pink Flamingo。
  4. 在1-3步骤后,有人尝试下Lawn Gnome订单。

显然,我留下未处理的问题和其他东西,但我们会接触这些。

原文链接:

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