SQL Server之创建表和定义数据完整性

创建表和定义数据完整性

本节将要介绍使用 T-SQL 来创建表和定义数据完整性的基础内容。你可以在 SQL Server 环境中随意运行本节包括的代码例子。

在阅读建表语句之前，应该记住：表是属于架构，而架构又是属于数据库的。例子中使用的数据库名称是 testdb ，使用的架构名称是 dbo 。要在你的 SQL Server 环境中创建一个名为 testdb 的数据库，可以运行以下代码：

IF DB_ID('testdb') IS NULL
    CREATE DATABASE testdb;

如果还不存在名为 testdb 的数据库，这段代码就会创建一个新的。 DB_ID 函数接受一个数据库名称作为输入参数，返回它的内部数据库 ID。如果输入名称指定的数据库不存在，这个函数将返回 NULL 。这是一种检查数据库是否存在的简单方法。注意：在这个简单的 CREATE DATABASE 语句中，采用了默认的文件设置（例如，区域和初始大小）。在生产环境中，通常应该显示指定所有需要的数据库和文件的设置。不过对于我们现在的目的来说，默认设置就够用了。

在例子中使用的架构是 dbo ，在每个数据库中都会自动创建这个架构。当用户没有显式指定默认架构时，就会将这个 dbo 作为默认架构。

创建表

以下代码在 testdb 数据库中创建一个名为 Employees 的表：

USE testdb;

IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL DROP TABLE dbo.Employees; 

CREATE TABLE dbo.Employees (
    empid INT NOT NULL,
    firstname VARCHAR (30) NOT NULL,
    lastname VARCHAR (30) NOT NULL,
    hiredate DATE NOT NULL,
    mgrid INT NULL,
    ssn VARCHAR (20) NOT NULL,
    salary money NOT NULL
);

USE 语句将当前的数据库上下文切换为 testdb 。在创建对象的脚本中加入 USE 语句，它的重要作用是确保要在正确的数据库中创建对象。

IF 语句调用 OBJECT_ID 函数来检查当前数据库中是否存在 Employees 表。 OBJECT_ID 函数接受一个对象名称和类型作为它的输入参数。这里，类型 'U' 代表用户表。如果匹配给定输入名称和类型的对象存在，这个函数就返回内部的对象 ID，否则返回 NULL 。如果该函数返回 NULL ，就可以知道检查的数据库对象是不存在的。在这个例子中，如果 Employees 表已经存在，代码就先删除（ DROP ）这个表，接着再创建一个新的。当然，也可以选择不同的处理方法，例如，当 Employees 表已经存在时，可以简单地不创建这个对象。

CREATE TABLE 语句负责定义前面提到的关系的主体。这个语句中先指定表的名称，接着在圆括号中定义它的各个属性（列）。

注意表名称使用了前面推荐的由两部分组成的名称 dbo.Employees 。如果省略了架构名称，SQL Server 将使用与运行这段代码的数据库用户相关联的默认架构。

对于表的每个属性，需要指定它的属性名称、数据类型和是否允许 NULL 数据值（NULLability）。

在 Employees 表中， empid （员工 ID）和 mgrid （经理 ID）定义为 INT （4 字节的整数类型）； firstname 、 lastname 和 ssn （社会保险号，social security number）定义为 VARCHAR （可变长度的字符串类型，指定最多支持的字符个数）； hiredate 定义为 DATE ， salary 定义为 MONEY 。注意： DATE 数据类型是 SQL Server 2008 新增加的。如果使用的是这一产品的早期版本，则应该使用 DATETIME 或 SMALLDATETIME 数据类型。

如果不显式指定一个列是否允许 NULL 值，SQL Server 则采用默认值。ANSI 规定：如果不指定一个列是否允许 NULL 值，则假设应该是 NULL （允许 NULL 值）。但 SQL Server 提供了一些设置可以改变这一默认行为。在这里强烈推荐在这种情况下要显式指定设置，不要依赖其默认值。而且，也强烈推荐将列定义为 NOT NULL ，除非有明显的原因而必须要支持 NULL 。即使认为一个列不允许 NULL 值，但没有用 NOT NULL 约束加以限制时， NULL 值仍然可以插入到这个列中。在 Employees 表中，除了 mgrid 列，其它所有列都定义为 NOT NULL 。 mgrid 属性为 NULL 值表示员工没有经理，例如企业 CEO 这种情况。

定义数据完整性

前面说过，关系模型带来的最大优点之一就是模型本身集成了数据完整性。作为模型的一部分而实施的数据完整性（也就是作为表定义的一部分）成为声明式（declarative）数据完整性。用代码来实施数据的完整性（例如用存储过程或触发器）成为过程式（procedural）数据完整性。

为属性选择的数据类型和是否允许为 NULL 值，甚至数据模型本身都是声明式数据完整性约束的例子。本节主要介绍声明式约束的其它例子，包括主键、唯一约束（ UNIQUE ）、外键、检查约束（ CHECK ），以及默认（ DEFAULT ）约束。当用 CREATE TABLE 语句创建表时，可以同时定义这些约束；或者在已经创建好表之后，用 ALTER TABLE 语句增加这些约束。除了 DEFAULT 约束以外，其它所有约束都可以定义为组合约束（即基于一个或多个约束）。

主键约束（Primary Key Constraints）

主键约束用来实施行的唯一约束，同时不允许约束属性取 NULL 值。约束属性中每一组唯一的值在表中只能出现一次，换句话说，表内的每行数据可以被唯一确定。如果视图在允许 NULL 值的列上定义主键约束，RDBMS 会拒绝。每个表只能定义一个主键。

下面以前面创建的 Employees 表为例，在它的 empid 列上定义一个主键约束：

ALTER TABLE dbo.Employees
    ADD CONSTRAINT PK_Employees
    PRIMARY KEY(empid);

定义好主键以后，就可以保证所有的 empid 值将是唯一而确定的。如果插入或更新一行违反约束的数据，RDBMS 就会拒绝操作，生成一个报错。

为了实施逻辑主键约束的唯一约束，SQL Server 将在幕后创建一个唯一的索引（unique index）。唯一索引是 SQL Server 为了实施唯一约束而采用的一种物理机制。也可以用索引（不一定是唯一索引）来加速查询的处理，避免对整个表进行不必要的扫描（类似于图书的索引）。

唯一约束（Unique Constraints）

唯一约束用来保证数据行的一个列（或一组列）的数据唯一，可以在数据库中实现关系模型的替换键（alternate key）的概念。与主键不同的是，在同一个表中可以定义多个唯一约束。此外，唯一约束也不限于只定义在 NOT NULL 列上。ANSI SQL 支持两种类型的唯一约束，一种是只允许在唯一约束列中有一个列值可以为 NULL ，另一种则允许多个 NULL 值列。SQL Server 只实现了前者。

以下代码在 Employees 表中定义了 ssn 列上的一个唯一约束：

ALTER TABLE dbo.Employees
    ADD CONSTRAINT UNQ_Employees_ssn
    UNIQUE(ssn);

和主键约束一样，SQL Server 也在幕后创建一个唯一索引，作为实施逻辑唯一约束的物理机制。

外键约束

外键约束用于实施引用完整性。这种约束在引用表（referencing table）的一组属性上进行定义，并指向被引用表（referenced table）中的一组候选键（主键或唯一约束）。注意：引用表和被引用表可能是同一张表。外键的目的是为了将外键列允许的值域限制为被引用列中现有的值。

以下代码创建了一个名为 Orders 的表，其主键定义在 orderid 列上：

IF OBJECT_ID('dbo.orders', 'U') IS NOT NULL
    DROP TABLE dbo.orders ; 

CREATE TABLE dbo.orders (
    orderid INT NOT NULL,
    empid INT NOT NULL,
    custid VARCHAR (10) NOT NULL,
    orderts DATETIME NOT NULL,
    qty INT NOT NULL,
    CONSTRAINT PK_Orders PRIMARY KEY (orderid)
);

如果现在想实施一个完整性规则，将 Orders 表的 empid 列支持的值域限制为现有的 Employees 表中 empid 列的值。为此，要在 Orders 表的 empid 列上定义一个外键约束，让它指向 Employees 表的 empid 列，如下所示：

ALTER TABLE dbo.orders 
    ADD CONSTRAINT FK_Orders_Employees 
    FOREIGN KEY (empid) REFERENCES dbo.Employees (empid);

类似的，如果想限制 Employees 表的 mgrid 列支持的值域为当前表中已存在的那些 empid 列的值，可以增加以下外键约束：

ALTER TABLE dbo.Employees 
    ADD CONSTRAINT FK_Employees_Employees 
    FOREIGN KEY (empid) REFERENCES dbo.Employees (empid);

前面两个例子演示了外键的基本定义，它们实施的引用操作称为“禁止操作（no action）”。禁止操作的含义是：当试图删除被引用表中的行，或更新被引用的候选键时，如果在引用表中存在相关的行，则此操作不能执行。例如，如果视图从 Employees 表中删除一个员工数据行，而 Orders 表中同时还存在与这个员工相关的订单数据行，RDBMS 将拒绝执行这样的操作，并生成报错信息。

可以定义具有级联操作的外键——为了当在引用表中存在相关数据行时可以删除被引用表中的数据行或更新被引用候选键属性。可以在外键定义中将 ON DELETE 和 ON UPDATE 选项定义为 CASCADE 、 SET DEFAULT 和 SET NULL 之类的操作。 CASCADE 的含义是：操作（删除或更新）将被级联到引用表中相关的行。例如， ON DELETE CASCADE 意味着当从被引用表中删除一行时，RDBMS 也将从引用表中删除相关的行。 SET DEFAULT 和 SET NULL 意味着会把相关行的外键属性分别设置为列的默认值或 NULL 值。

检查约束（CHECK）

检查约束用于定义在表中输入或修改一行数据之前必须满足一个谓词。例如，以下的检查约束可以保证 Employees 表中的 salary 列只支持正数：

ALTER TABLE dbo.Employees
    ADD CONSTRAINT CHK_Employees_salary
    CHECK(salary > 0);

如果试图用非正数的 salary 值插入或更新数据行，RDBMS 将拒绝这样的操作。注意：当谓词计算结果为 FALSE 时，检查约束将拒绝插入或更新数据行的操作。当谓词计算结果为 TRUE 或 UNKNOWN 时，RDBMS 将会接受对数据行的修改。例如， salary 为 -1000 将被拒绝，而 salary 为 1000 和 NULL 都可以被接受。

当增加 CHECK 和 FOREIGN KEY 约束时，可以指定一个 WITH NOCHECK 选项，告诉 RDBMS 不必对现有的数据进行约束检查。通常认为这是一种不好的做法，因为这样不能保证数据的一致性。

默认约束（DEFALUT）

默认约束与特定的属性关联。当插入一行 数据是，如果没有为属性显示指定明确的值，就可以用一个表达式作为其默认值。例如，以下代码为 orderts 属性定义了一个默认约束（表示订单的时间戳）：

ALTER TABLE dbo.Orders
    ADD CONSTRAINT CHK_Orders_orderts
    DEFAULT(CURRENT_TIMESTAMP) FOR orderts;

默认表达式会调用 CURRENT_TIMESTAMP 函数，由它返回当前的日期和时间值。在定义好默认表达式以后，当在 Orders 表插入一行数据，而且没有显示指定 orderts 属性值时，SQL Server 将把这个属性值设置为 CURRENT_TIMESTAMP 函数返回的值。