T-SQL的CREATE TABLE语法(下)

 

PRIMARY KEY 约束

    * 一个表只能包含一个 PRIMARY KEY 约束。
    * 由 PRIMARY KEY 约束生成的索引不能使表中的非聚集索引超过 249 个，聚集索引超过 1 个。
    * 如果没有在 PRIMARY KEY 约束中指定 CLUSTERED 或 NONCLUSTERED，并且没有为 UNIQUE 约束指定聚集索引，则将对该 PRIMARY KEY 约束使用 CLUSTERED。
    * 在 PRIMARY KEY 约束中定义的所有列都必须定义为 NOT NULL。如果没有指定为空性，加入 PRIMARY KEY 约束的所有列的为空性都将设置为 NOT NULL。

UNIQUE 约束

    * 如果 UNIQUE 约束中没有指定 CLUSTERED 或 NONCLUSTERED，则默认为 NONCLUSTERED。
    * 每个 UNIQUE 约束都生成一个索引。由 UNIQUE 约束生成的索引不能使表中的非聚集索引超过 249 个，聚集索引超过 1 个。

FOREIGN KEY 约束

    * 如果在 FOREIGN KEY 约束的列中输入非 NULL 值，则此值必须在被引用的列中存在，否则将返回违反外键约束的错误信息。
    * FOREIGN KEY 约束应用于前面所讲的列，除非指定了源列。
    * FOREIGN KEY 约束仅能引用位于同一服务器上的同一数据库中的表。数据库间的引用完整性必须通过触发器实现。有关更多信息，请参见 CREATE TRIGGER。
    * FOREIGN KEY 可以引用同一表中的其它列（自引用）。
    * 列级 FOREIGN KEY 约束的 REFERENCES 子句仅能列出一个引用列，且该列必须与定义约束的列具有相同的数据类型。
    * 表级 FOREIGN KEY 约束的 REFERENCES 子句中引用列的数目必须与约束列列表中的列数相同。每个引用列的数据类型也必须与列表中相应列的数据类型相同。
    * 如果 timestamp 类型的列是外键或被引用键的一部分，则不能指定 CASCADE。
    * 可以在相互间具有引用关系的表上组合使用 CASCADE 和 NO ACTION。如果 SQL Server 遇到 NO ACTION，将终止执行语句并回滚相关的 CASCADE 操作。当 DELETE 语句导致 CASCADE 和 NO ACTION 组合操作时，在 SQL Server 检查 NO ACTION 操作之前将执行所有 CASCADE 操作。
    * 一个表最多可包含 253 个 FOREIGN KEY 约束。
    * 对于临时表不强制 FOREIGN KEY 约束。
    * 每个表在其 FOREIGN KEY 约束中最多可以引用 253 个不同的表。
    * FOREIGN KEY 约束只能引用被引用表的 PRIMARY KEY 或 UNIQUE 约束中的列或被引用表上 UNIQUE INDEX 中的列。

DEFAULT 定义

    * 每列只能有一个 DEFAULT 定义。
    * DEFAULT 定义可以包含常量值、函数、SQL-92 niladic 函数或 NULL。下表显示 niladic 函数及其在执行 INSERT 语句时返回的默认值。
      SQL-92 niladic 函数     返回的值
      CURRENT_TIMESTAMP     当前日期和时间。
      CURRENT_USER     执行插入操作的用户名。
      SESSION_USER     执行插入操作的用户名。
      SYSTEM_USER     执行插入操作的用户名。
      USER     执行插入操作的用户名。
    * DEFAULT 定义中的 constant_expression 不能引用表中的其它列，也不能引用其它表、视图或存储过程。
    * 不能在数据类型为 timestamp 的列或具有 IDENTITY 属性的列上创建 DEFAULT 定义。
    * 如果用户定义数据类型绑定到默认对象，则不能在该用户定义数据类型的列上创建 DEFAULT 定义。

CHECK 约束

    * 列可以有任意多个 CHECK 约束，并且约束条件中可以包含用 AND 和 OR 组合起来的多个逻辑表达式。列上的多个 CHECK 约束按创建顺序进行验证。
    * 搜索条件必须取值为布尔表达式，并且不能引用其它表。
    * 列级 CHECK 约束只能引用被约束的列，表级 CHECK 约束只能引用同一表中的列。

      当执行 INSERT 和 DELETE 语句时，CHECK CONSTRAINTS 和规则具有相同的数据验证功能。
    * 当列上存在规则和一个或多个 CHECK 约束时，将验证所有限制。

其它约束信息

    * 为约束创建的索引不能用 DROP INDEX 语句除去；必须用 ALTER TABLE 语句除去约束。可以用 DBCC DBREINDEX 语句重建为约束创建的并由其使用的索引。
    * 约束的名称必须符合标识符规则，但其名称的首字符不能为 #。如果没有提供 constraint_name，则使用系统生成的名称。约束名将出现在所有与违反约束有关的错误信息中。
    * 当 INSERT、UPDATE 或 DELETE 语句违反约束时，将终止执行该语句。但将继续处理事务（如果此语句为显式事务的组成部分）。可以通过检查系统函数 @@ERROR，在事务定义中使用 ROLLBACK TRANSACTION 语句。

如果某个表具有 FOREIGN KEY 或 CHECK CONSTRAINTS 及触发器，则将在触发器执行前先检查约束条件。

若要获得关于表及其列的报表，请使用 sp_help 或 sp_helpconstraint。若要重命名表，请使用 sp_rename。若要获得与表相关的视图和存储过程的报表，请使用 sp_depends。

通常情况下，为表和索引分配空间时，每次以一个扩展盘区为增量单位。当创建表或索引时，首先从混合扩展盘区为其分配页，直到它具有足够的页填满一个统一扩展盘区。当有足够的页填满统一扩展盘区后，每当当前分配的扩展盘区填满时，将再为其分配另一个扩展盘区。若要获得关于由表分配和占用的空间量的报表，请执行 sp_spaceused。
表定义中的为空性规则

列的为空性规则决定该列中是否允许以空值 (NULL) 作为其数据。NULL 不是零或空白：它表示没有输入任何内容，或提供了一个显式 NULL 值，通常表示该值未知或不适用。

当用 CREATE TABLE 或 ALTER TABLE 语句创建或更改表时，数据库或会话设置会影响且可能替代列定义中数据类型的为空性。建议始终将列显式定义为非计算列的 NULL 或 NOT NULL，如果使用用户定义数据类型，则建议允许该列使用此数据类型的默认为空性。

在没有显式指定时，列的为空性遵循以下规则：

    * 如果该列以用户定义数据类型定义：
          o SQL Server 使用在创建数据类型时指定的为空性。使用 sp_help 可获得该数据类型的默认为空性。
    * 如果该列以系统提供的数据类型定义：
          o 如果系统提供的数据类型只有一个选项，则优先使用该选项。timestamp 数据类型只能定义为 NOT NULL。
          o 如果 sp_dbcmptlevel 的设置是 65 或更小，且列没有显式定义 NULL 或 NOT NULL，则 bit 数据类型默认为 NOT NULL。有关更多信息，请参见 sp_dbcmptlevel。
          o 如果有任何会话设置为 ON（用 SET 语句打开），那么：

            如果 ANSI_NULL_DFLT_ON 是 ON，则指派 NULL。

            如果 ANSI_NULL_DFLT_OFF 是 ON，则指派 NOT NULL。
          o 如果配置了任何数据库设置（用 sp_dboption 更改），那么：

            如果 ANSI null default 为 true，则指派 NULL。

            如果 ANSI null default 为 false，则指派 NOT NULL。
    * 当会话的两个 ANSI_NULL_DFLT 选项都未设置，且数据库设置为默认值（ANSI null default 为 false）时，将指派 SQL Server 的默认设置 NOT NULL。
    * 如果该列是计算列，则其为空性总是由 SQL Server 自动确定。使用 COLUMNPROPERTY 函数（AllowsNull 属性）查找这类列的为空性。

说明   默认情况下，SQL Server ODBC 驱动程序和用于 SQL Server 的 Microsoft OLE DB 提供程序都将 ANSI_NULL_DFLT_ON 设置为 ON。ODBC 和 OLE DB 用户可以在 ODBC 数据源中配置该设置，或通过应用程序设置的连接特性或属性配置该设置。

权限

CREATE TABLE 权限默认授予 db_owner 和 db_ddladmin 固定数据库角色成员。db_owner 固定数据库角色成员和 sysadmin 固定服务器角色成员可以将 CREATE TABLE 权限转让给其他用户。
示例
A. 使用 PRIMARY KEY 约束

下例显示在示例数据库 pubs 的 jobs 表中，job_id 列中具有聚集索引的 PRIMARY KEY 约束的列定义；此例由系统提供约束名。

job_id   smallint
      PRIMARY KEY CLUSTERED

下例显示如何为 PRIMARY KEY 约束提供名称。此约束用于 employee 表中的 emp_id 列。此列基于用户定义数据类型。

emp_id   empid
      CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED

B. 使用 FOREIGN KEY 约束

FOREIGN KEY 约束用于引用其它表。FOREIGN KEY 可以是单列键或多列键。下例显示 employee 表上引用 jobs 表的单列 FOREIGN KEY 约束。对于单列 FOREIGN KEY 约束，只需要 REFERENCES 子句。

job_id   smallint      NOT NULL
      DEFAULT 1
      REFERENCES jobs(job_id)

也可以显式使用 FOREIGN KEY 子句并复述列特性。注意在这两个表中列名不必相同。

FOREIGN KEY (job_id) REFERENCES jobs(job_id)

多列键约束作为表约束创建。在 pubs 数据库中，sales 表包含多列 PRIMARY KEY。下例显示如何从其它表中引用此键（可选择显式约束名）。

CONSTRAINT FK_sales_backorder FOREIGN KEY (stor_id, ord_num, title_id)
   REFERENCES sales (stor_id, ord_num, title_id)

C. 使用 UNIQUE 约束

UNIQUE 约束用于强制非主键列的唯一性。PRIMARY KEY 约束列自动包含唯一性限制；但是，UNIQUE 约束允许存在空值。下例显示表 authors 中名为 pseudonym 的列。该列强制作者笔名必须唯一。

pseudonym varchar(30)   NULL
UNIQUE NONCLUSTERED

下例显示在 stor_id 实际上是 PRIMARY KEY 的 stores 表中，stor_name 列和 city 列上创建的 UNIQUE 约束；同一个城市中的商店不应同名。

CONSTRAINT U_store UNIQUE NONCLUSTERED (stor_name, city)

D. 使用 DEFAULT 定义

使用 INSERT 和 UPDATE 语句时，如果没有提供值，则默认值会提供值。在 pubs 数据库中，使用了许多 DEFAULT 定义以确保输入有效的数据或占位符。

在 jobs 表上，当没有显式输入实际的描述信息时，默认的字符串将提供描述信息（列 job_desc）。

DEFAULT ''''New Position - title not formalized yet''''

在 employee 表中，员工可以受雇于子公司或母公司。如果没有显式提供公司信息，则输入母公司（注意在表定义中可以嵌套注释，如下所示）。

DEFAULT (''''9952'''')
/* By default the Parent Company Publisher is the company
to whom each employee reports. */

除了常量以外，DEFAULT 定义还可以包含函数。使用下例获取输入项的当前日期：

DEFAULT (getdate())

niladic 函数也可以提高数据的完整性。若要跟踪插入行的用户，请使用 niladic 函数 USER（niladic 函数不使用括号）：

DEFAULT USER

E. 使用 CHECK 约束

下例显示对输入到 jobs 表中的 min_lvl 列和 max_lvl 列的值的限制。这两个约束都未命名：

CHECK (min_lvl >= 10)

与

CHECK (max_lvl <= 250)

下例显示对输入到 employee 表的 emp_id 列中的字符数据具有模式限制的命名约束。

CONSTRAINT CK_emp_id CHECK (emp_id LIKE
   ''''[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]'''' OR
   emp_id LIKE ''''[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'''')

下例指定 pub_id 必须在特定的列表中或遵循给定的模式。此约束用于 publishers 表中的 pub_id 列。

CHECK (pub_id IN (''''1389'''', ''''0736'''', ''''0877'''', ''''1622'''', ''''1756'''')
   OR pub_id LIKE ''''99[0-9][0-9]'''')

F. 完整的表定义

下例显示 pubs 数据库中所创建的三个表（jobs、employee 和 publishers）的完整表定义，其中包含所有的约束定义。

/* ************************** jobs table ************************** */
CREATE TABLE jobs
(
   job_id  smallint
      IDENTITY(1,1)
      PRIMARY KEY CLUSTERED,
   job_desc        varchar(50)     NOT NULL
      DEFAULT ''''New Position - title not formalized yet'''',
   min_lvl tinyint NOT NULL
      CHECK (min_lvl >= 10),
   max_lvl tinyint NOT NULL
      CHECK (max_lvl <= 250)
)

/* ************************* employee table ************************* */
CREATE TABLE employee
(
   emp_id  empid
      CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
      CONSTRAINT CK_emp_id CHECK (emp_id LIKE
         ''''[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]'''' or
         emp_id LIKE ''''[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]''''),
      /* Each employee ID consists of three characters that
      represent the employee''''s initials, followed by a five
      digit number ranging from 10000 through 99999 and then the
      employee''''s gender (M or F). A (hyphen) - is acceptable
      for the middle initial. */
   fname   varchar(20)     NOT NULL,
   minit   char(1) NULL,
   lname   varchar(30)     NOT NULL,
   job_id  smallint        NOT NULL
      DEFAULT 1
      /* Entry job_id for new hires. */
      REFERENCES jobs(job_id),
   job_lvl tinyint
      DEFAULT 10,
      /* Entry job_lvl for new hires. */
   pub_id  char(4) NOT NULL
      DEFAULT (''''9952'''')
      REFERENCES publishers(pub_id),
      /* By default, the Parent Company Publisher is the company
      to whom each employee reports. */
   hire_date       datetime        NOT NULL
      DEFAULT (getdate())
      /* By default, the current system date is entered. */
)

/* ***************** publishers table ******************** */
CREATE TABLE publishers
(
   pub_id  char(4) NOT NULL
         CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED
         CHECK (pub_id IN (''''1389'''', ''''0736'''', ''''0877'''', ''''1622'''', ''''1756'''')
            OR pub_id LIKE ''''99[0-9][0-9]''''),
   pub_name      varchar(40)     NULL,
   city         varchar(20)     NULL,
   state      char(2) NULL,
   country      varchar(30)     NULL
            DEFAULT(''''USA'''')
)

G. 在列中使用 uniqueidentifier 数据类型

下例创建含有 uniqueidentifier 列的表。该表使用 PRIMARY KEY 约束以确保用户不会在表中插入重复的值，

.s0%ON6#D理育

并在 DEFAULT 约束中使用 NEWID() 函数为新行提供值。

CREATE TABLE Globally_Unique_Data
(guid uniqueidentifier
   CONSTRAINT Guid_Default
   DEFAULT NEWID(),
Employee_Name varchar(60),
CONSTRAINT Guid_PK PRIMARY KEY (Guid)
)

H. 对计算列使用表达式

下例显示如何使用表达式 ((low + high)/2) 计算 myavg 计算列。

CREATE TABLE mytable
   (
    low int,
    high int,
    myavg AS (low + high)/2
   )

I. 对计算列使用 USER_NAME 函数

下例在 myuser_name 列中使用 USER_NAME 函数。

CREATE TABLE mylogintable
   (
    date_in datetime,
    user_id int,
    myuser_name AS USER_NAME()
   )

J. 使用 NOT FOR REPLICATION

下例显示如何在订阅了复制的表中使用 IDENTITY 属性。此表包含 CHECK 约束，7无hw网yP*)4?r以确保此系统生成的 SaleID 值不会增长到为复制发布服务器指派的范围内。

CREATE TABLE Sales
   (SaleID INT IDENTITY(100000,1) NOT FOR REPLICATION,
             CHECK NOT FOR REPLICATION (SaleID <= 199999),
    SalesRegion CHAR(2),
   CONSTRAINT ID_PK PRIMARY KEY (SaleID)
   )

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/219982/viewspace-584393/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/219982/viewspace-584393/