0

SQL的CREATE TABLE语法(下...

已有2036人阅读此文 - - 未分类 - luoyy -

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)
)

期待你一针见血的评论,Come on!

发表评论: