数据库设计(2/9):域,约束和默认值(Domains, Constraints and Defaults)

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


对于数据库开发人员,SQL数据类型和域的清楚了解是基本要求,但不是基础。如果你选择了最合适的数据类型,它可以避开很多错误。此外,然后如果你通过约束尽可能精确的定义数据域,你会捕获各种这样的问题,那就是否则会困扰应用开发人员的工作。

第1篇,我们为它们是什么而命名数据元,并为它们分类。因此现在我们知道,我们不能用通用的,神奇的,普遍的“id”作为数据元。它必须是一个在架构里有确切含义的确切名称。更好的是,名称应该是企业范围,行业范围或在全球的优先排序里。

在第2篇里,我们会决定通过选择合适的域把那些数据放入计算机。域的想法要回到Codd博士。嗯,实际上在这个好博士(Good Doctor)之前,因为它来自数学。域像程序设计里的数据类型,但涉及更多。域有合法的在它的值上完成的运算符,除此之外还有一个数据类型。例如,我可以使用INTEGER在数据库里记录温度;那是个数据类型。当我看到100,那是个值。同时,我可以加,减,乘和除整数,但在温度上这样做没有意义。你不能把体温是36°的人放一起来烧水。模型的类型决定允许什么操作。但我需要知道数字是否以华氏度(Fahrenheit (°F)),摄氏度(Celsius (°C))还是绝对温度(Kelvin (°K));那是计量单位。把这些放在一起,你就有了域。

标准SQL有CREATE DOMAIN语句,它创建的架构对象不是真正的域。它只是个我们即将讨论的缩写。

SQL有3大类数据类型:

  1. 数值(Numberic)
  2. 字符串(String)
  3. 日期(Temporal)

数值型分为精确和近似的。精确数值有INTEGER, SMALLINT, DECIMAL, NUMERIC和BIGINT。它们保存精确的数值并有明确的计算运算符。近似数值有REAL, FLOAT 和 DOUBLE PRECISION(双精度)。这些是浮点数,它们会有四舍五入的问题;近来IEEE浮点标准普遍使用。SQL里也不例外;大多数其它可以进行计算的编程语言使用全部,或者使用这些类型的一些,因为它们在SQL 发明很早以前就嵌入了计算机硬件。PC和迷你计算机用户很可能不知道二进码十进制(BCD(Binary Coded Decimal))数,但多年来它们是商业电脑主机的组成部分。如果你不能理解这些类型,可以google下它们。

字符串类型分为定长和变长。定长字符串是CHAR (n) 和NCHAR(n),这里(n)是它们的长度。NCHAR(n)是“National Character”的简称,它的真正含义是来自Unicode已经实行的任何语言的任何字符。CHAR (n) 是本地ASCII字符集。变长字符串不会像定长字符加个空白字符。对列选择长度和字符集是你在使用它之前,真的要考虑的约束。太短的列会阻止真正的数据,同时太长的列会引入虚假的数据。我最喜欢的一个技巧,当我看到一个以NVARCHAR(255)定义的列,用它来加载中文的心经。这是禅宗佛教(Zen Buddhism)的经典经文。如果我不能教他们SQL,我会带它们启蒙。

日期类型分为纯日期时间和间隔类型。日期时间类型分为日期和时间。它表示时间上一个点。日期类型包含年,月和日。时间类型包括时,分,秒和10进制的次秒。放一起,在标准SQL里它们组成TIMESTAMP数据类型,SQL Server称它们为DATETIME。间隔类型是像天,时,分和秒的时间持续。SQL Server不把它们作为特定类型来表示,使用整数的函数来得到类似结果。

对所有数据类型的四舍五入和截断实现定义。所有数据类型允许NULL,同时在SQL标准里,对所有的数据类型有一组核心的函数,提供所有有的属性扩展和细节实现。例如,SQL Server有BIT数据类型,它是一个精确的数字,只允许0, 1, NULL值。你也会找到更多的数据类型,但这“三个值”会完成你的大多数工作。

当你在数据上你要做计算时,使用数值类型。那就是说像数量,个数,合计等等数据元。如果你需要进行除简单加减外的计算,对于四舍五入和溢出定义一些额外的小数位。

对于要排序的嗲吗你也会使用数值类型。例子就是餐厅的星级(例如1星没有2星的好,2星的没有3星的好,以此类推)。不要为不进行计算或比较的尺度使用数值类型。这个错误的常见例子是邮编(ZIP code);第一个数字0有它的意思,你不能在它们上面进行计算,也不能进行排序。标签数用来表示层级。

对文本,名称和编码体系使用字符串可以用正则表达式表示。例如,邮政编码应该用CHAR(5)定义。不要尝试计算它们;在COBOL里可以,在SQL里不行。

对于日期数据使用日期数据类型。没错,听起来太明显,我都没必要说。但其中一个最常见的设计错误是对于日期和时间数据使用字符串。当然这样做的人不会写约束来阻止像“2010-02-31”的日期或者进行简单时间计算函数。他们已经犯了设计错误,把显示格式放入数据库,而不是前端。

实数和时间是连续的例子,其它数据类型是离散的。离散范围是任何两个不同值之间有限的数字(很可能0)。例如整数{4,9},在它们之间有{5,6,7,8}。有一期儿童电视节目iCarly里,女主持人说服另一个小孩,在5和6之间有一个叫Dirf的新的数字。这个笑话太滑稽了,因为很明显它是错误的。

连续性是数学结构,在任何两个不同值之间有无限个数据值。你总可以添加越来越多的小数到实数或没有任何限制的时间。浮点数有内建的功能来处理四舍五入和计算的问题,但是时间数据不行。通常这就是说你要用一对值来模式化事件(开始事时间,结束时间)。如果事件还是当前的,那么使用NULL作为结束事件值。然后在应用程序里你可以使用COALESCE()函数来转化NULL为CURRENT_DATE或其它有意义的值。

约束是表里的列像记录里的字段的一个原因。约束是在列里约束值的陈述句。最重要的一个是NOT NULL。首次用它来申明每列,然后如果你决定允许NULL,注释行申明来解释说明在上下文里的意思。例如:

sale_start_date DATE NOT NULL,
sale_end_date DATE, – sale is still in progress 

CHECK(<涉及列的谓语>)是最简单的行级别约束。然和有效的谓语可以使用,一些典型的使用会是:

 sex_code SMALLINT NOT NULL
 CHECK (sex_code IN (0,1,2,9)),

body_temperature DECIMAL (3,1) NOT NULL
 CHECK (body_temperature BETWEEN 0.0 AND 45.0),

airport_code CHAR(3) NOT NULL
 CHECK (airport_code = UPPER (airport_code)),

zip_code CHAR(5) NOT NULL
 CONSTRAINT Valid_ZIP_Code
 CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]'), 

你也可以命名约束,如上所示。这是个很好的做法,因为它让错误信息更容易阅读。使用CASE表达式你会得到很多有意思的东西,对于其它复杂规则使用if-then逻辑的其它谓语,例如:

 floob_score INTEGER CHAR(5) NOT NULL
CHECK (CASE WHEN floob_score NOT BETWEEN 1 AND 99 THEN 'F'
            WHEN floob_score = 42
             AND fuzz_nbr = 17
            THEN 'T' ELSE 'F' END = T'), 

作为练习,写一个CASE表达式来验证是否为数字。表达式会很长但不难。

除了数据完整性,约束还会为你做其它好事。优化器可以用到它们来提高你的查询,插入,更新和删除。它们帮你节约很多前端代码;在这里一次搞定,就不用在很多应用程序里反复做,不管现在还是将来。它们保证所有的前端程序使用同样定义的数据元。

CHECK()约束也可以放在表级别。例如:

sale_start_date DATE NOT NULL,
sale_end_date DATE, – sale is still in progress
CONSTRAINT Validate_Sale_Duration
CHECK (sale_start_date <= sale_end_date), 

表约束涉及到两个或更多的列。在标准SQL里,你也可以有引用其它表的CHECK()约束,但现在我会跳过这个;它没有广泛应用,或不是SQL Server的组合。

SQL的策略是它我们用NULL进行比较时,逻辑值的结果是UNKNOWN,不是TRUE或FALSE。但在CHECK()约束里,TRUE和UNKNOWN同等对待。我们把质疑的好处给UNKNOW。

在行里定义里的最后选项是DEFAULT子句(默认值)。技术上来说它不是域定义的组成,它非常有用。在标准SQL里,它会在数据类型后立即出现,在行定义里,大多数对于它的放置位置都是自由的。它是个常量值或者调用合适数据类型的系统值。最常见的例子是,对于数字和字符串数据在编码架构里的默认值,对于日期数据的CURRENT_TIMESTAMP和CURRENT_DATE。例如ISO的性别编码使用0作为“未知”,1为”男性“,2为”女性“,9作为合法的人(lawful person )(企业和其它组织会在上下文里识别为”法人(legal persons)“)。例如,我们会像这样申明行:

 sex_code SMALLINT NOT NULL
 DEFAULT 0
 CHECK (sex_code IN (0,1,2,9)),
 sale_start_date DATE DEFAULT CURRENT_DATE NOT NULL

默认值的目的是在没有直接给值的地方提供值。这通常使用INSERT INTO语句完成,在这里你必须构建整个行,但你不想暴露所有行给用户,或者你想节省一些编程工作。总是提供默认不太可能,当你有的时候再这样做。

现在我们会创建行,在第3篇里我们需要组合行到表里。那里我们约束的其它类型,它会应用到列,不止单行。

原文链接:

http://www.sqlservercentral.com/articles/Database+Design/69926/

posted @ 2016-05-09 08:01  Woodytu  阅读(3964)  评论(1编辑  收藏  举报