SQL建立与使用默认值、条件约束及规则

SQL建立与使用默认值、条件约束及规则

1、默认值

使用CREATE TABLE建立默认值

USE MyDB
CREATE TABLE MyTABLE
(
columnA char(15) NULL DEFAULT 'n/a',
columnB int NULL DEFAULT 0
)
GO

　
使用ALTER TABLE命令可以修改数据行中的默认值定义或新增一数据行。若要更改已经定义的默认值数据行，首先必须删除已经有的默认值，然后新增一个新的默认值

如果用CREATE TABLE命令建立未命名的默认值，SQL Server将自动替默认值命名。要知道SQL Server为默认值分配了什么名字，以便可以使用T-SQL删除它，您可执行sp_help程序如下：

USE MyDB
GO
sp_help MyTable
GO

假设我们要把columnA的默认值从n/a改成not applicable。记住首先必须删除存在的默认值然后再新增一个新的。下述命令即可删除默认值：

ALTER TABLE MyTable
DROP CONSTRAINT DF_MyTable_columnA_2B3F6F97

现在您可以使用下述命令新增一个默认值，这次由我们自己命名:

ALTER TABLE MyTable
ADD CONSTRAINT DF_MyTable_columnA
DEFAULT "Not applicable" FOR columnA
GO
当变更已存在的默认值时，所有现存的列将保持原始的默认值。只有新插入的列会使用新的默认值。

用ALTER TABLE命令为已有的数据表新增完整的新数据行，如下所示：

ALTER TABLE MyTable
ADD columnC tinyint NOT NULL DEFAULT 13
GO

用默认值而不是NULL插入已存在的列，则应该使用DEFAULT中的WITH VALUES选项，如下所示：

ALTER TABLE MyTable
ADD columnC tinyint NULL DEFAULT 13 WITH VALUES
GO

WITH VALUES命令会强行使MyTable中所有现存列的新数据行接受默认值13来代替原来的NULL值。

CREATE DEFAULT和sp_bindefault
　
如果要在不同的数据表中使用相同的默认值数据行，此方法则较为有效。

使用CREATE DEFAULT的语法如下：

CREATE DEFAULT default_name AS constant_expression

sp_bindefault的语法如下：

sp_bindefault 'default_name' table.column | user_defined_datatype
[", futureonly"]

示例：

USE MyDB
GO
CREATE DEFAULT DF_not_applicable AS 'n/a'

GO
sp_bindefault "DF_not_applicable", "MyTable.columnA"
GO

如果没有指定futureonly，SQL Server将默认值系结到所有已经存在的和新建立的使用者自订类型的数据行上

例如，让我们建立一个名称为area_code的使用者自订型别和名称为DF_area_code的默认值对象，其值为786；然后系结默认值到该使用者自订数据型别上。因为这是新的使用者自订数据型别，因此目前还没有数据行，也就不需要futureonly选项

sp_addtype "area_code", "char(3)", "NOT NULL"
GO
CREATE DEFAULT DF_area_code AS 786
GO
sp_bindefault "DF_area_code", "area_code", "futureonly"
GO

要检视 预设 对象的数据型别，可使用sp_help系统程序

sp_unbindefault
　
例如要解除MyTable中与col

umnA数据行系结的默认值：sp_unbindefault如下：

sp_unbindefault "MyTable.columnA"
GO

从使用者自订的数据型别area_code中解除系结默认值：

sp_unbindefault "area_code"
GO

当执行以上程序，所有已经由使用者自订数据型别area_code的默认值属性将会同时移除。

同样的，只要在不删除预设对象的情形下，您可以随意地解除或系结某个数据行的预设。使用DROP DEFAULT陈述式，可以完全删除一个 预设 对象

如下所示：

DROP DEFAULT DF_area_code
GO

一旦删除了预设对象，就无法再取回。如果要再次使用，必须使用CREATE DEFAULT重新建立对象。

　
在 默认值 储存格中输入字符串，必须放在单引号中，否则储存时会显示SQL Server的错误讯息。


2条件约束
　
条件约束用于自动维护数据的完整性。举个例子，您可以将一个整数数据行条件约束在1到100的范围内，那么超出此范围的数值则无法被接受

条件约束的五种类型为NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY和CHECK

使用T-SQL建立和修改 条件约束
　
NOT NULL
　
NOT NULL条件约束相当简单

UNIQUE

UNIQUE条件约束用以确保一个或多个数据行中没有重复的数值

要用T-SQL为数据表建立UNIQUE条件约束，须执行CREATE TABLE或ALTER TABLE命令

例如

CREATE TABLE customer
(
first_name char(20) NOT NULL,
mid_init char(1) NULL,
last_name char(20) NOT NULL,
SSN char(11) NOT NULL UNIQUE CLUSTERED,
cust_phone char(10) NULL
)
GO

例子
CREATE TABLE customer
(
first_name char(20) NOT NULL,
mid_init char(1) NULL,
last_name char(20) NOT NULL,
SSN char(11) NOT NULL UNIQUE CLUSTERED,
cust_phone char(10) NULL,
CONSTRAINT UQ_full_name UNIQUE NONCLUSTERED (first_name, mid_init, last_name)
)
GO

新增数据行条件约束和数据表条件约束的两组命令：

ALTER TABLE customer
ADD CONSTRAINT UQ_ssn UNIQUE CLUSTERED(SSN)
GO

ALTER TABLE customer
ADD CONSTRAINT UQ_full_name UNIQUE NONCLUSTERED (first_name, mid_init, last_name)
GO

要用T-SQL来修改数据行或数据表中已有的UNIQUE条件约束，必须先删除条件约束再重新建立

主索引键
　
CREATE TABLE customer
(
first_name char(20) NOT NULL,
mid_init char(1) NULL,
last_name char(20) NOT NULL,
SSN char(11) PRIMARY KEY,
cust_phone char(10) NULL
)
GO
另一种可行的方法是以增加CONSTRAINT关键词来命名。使用下面的命令将主索引键命名为PK_SSN：

CREATE TABLE customer
(
first_name char(20) NOT NULL,
mid_init char(1) NULL,
last_name char(20) NOT NULL,
SSN char(11) CONSTRAINT PK_SSN PRIMARY KEY,
cust_phone char(10) NULL
)
GO

您也可以在定义了所有数据表的数据行后，再指定PRIMAR

Y KEY条件约束。数据行名称必须在括号中，并在CONSTRAINT后指定，语法如下所示：

CREATE TABLE customer
(
first_name char(20) NOT NULL,
mid_init char(1) NULL,
last_name char(20) NOT NULL,
SSN char(11),
cust_phone char(10) NULL,
CONSTRAINT PK_SSN PRIMARY KEY (SSN)
)
GO

以下为替customer资料表新增PRIMARY KEY

ALTER TABLE customer
ADD CONSTRAINT PK_SSN PRIMARY KEY CLUSTERED (SSN)
GO

要删除PRIMARY KEY条件约束，须使用ALTER TABLE命令和DROP CONSTRAINT陈述式

ALTER TABLE customer
DROP CONSTRAINT PK_SSN
GO

注意，只有在DROP CONSTRAINT的陈述式中，需要条件约束名称。要使用T-SQL命令修改数据表中现存的PRIMARY KEY条件约束，必须先使用ALTER TABLE...DROP CONSTRAINT删除现存条件约束和ALTER TABLE... ADD CONSTRAINT陈述式来新增条件约束以修改资料表。

外部索引键
　
首先建立一个数据表，命名为items，其中的item_id数据行具主索引键，如下所示：

CREATE TABLE items
(
item_name char(15) NOT NULL,
item_id smallint NOT NULL IDENTITY(1,1),
price smallmoney NULL,
item_desc varchar(30) NOT NULL DEFAULT 'none'
CONSTRAINT PK_item_id PRIMARY KEY (item_id)
)
GO

建立inventory的数据表，其中有称为FK_item_id的外部索引键，此外部索引键引用items数据表的item_id

如下所示：

CREATE TABLE inventory
(
store_id tinyint NOT NULL,
item_id smallint NOT NULL,
item_quantity tinyint NOT NULL,
CONSTRAINT FK_item_id FORGIEN KEY(item_id)
REFERENCES items(item_id)
)
GO

下面先删除inventory数据表的旧条件约束，然后新增条件约束的命令：
ALTER TABLE inventory
DROP CONSTRAINT FK_item_id
GO

ALTER TABLE inventory
ADD CONSTRAINT FK_item_id FOREIGN KEY (item_id)
REFERENCES items(item_id)
GO

当您在现存资料行中新增FOREIGN KEY条件约束，SQL Server会检查数据表中现存的数据列，以确保除NULL值外，外部索引键数据行的值符合参照资料表的PRIMARY KEY条件约束或UNIQUE条件约束。当建立FOREIGN KEY条件约束时，可以使用ALTER TABLE的WITH NOCHECK选项，那么SQL Server就不会去验证现有的值，如下所示：

ALTER TABLE inventory
WITH NOCHECK ADD CONSTRAINT FK_item_id
FOREIGN KEY (item_id)
REFERENCES items(item_id)
GO

WITH NOCHECK选项可避免SQL检查数据表中现存列的值。如此，无论现有值为何，都可以新增条件约束到数据表上。新增条件约束后，外部索引键的完整性也会增强。
________________________________________
注意
使用WITH NOCHECK选项时要小心。因为当您预计更新现存数据，但是现存数据中却包含与条件约束冲突的值，您将无法更新现存数据。
________________________________________

您也可以控制是否启用FOREIGN

KEY条件约束。NOCHECK关键词表示忽略条件约束；CHECK关键词表示条件约束生效。

ALTER TABLE inventory
NOCHECK CONSTRAINT FK_item_id -- 使条件约束无效
GO

--在此插入您需要的数据列
ALTER TABLE inventory
CHECK CONSTRAINT FK_item_id -- 重新回复条件约束
GO
________________________________________
说明
尽量不要插入与FOREIGN KEY条件约束冲突的资料行。否则可能导致将来数据表的有冲突的数据列无法被更新。
________________________________________
CHECK
　
CHECK条件约束用于限制数据行中值的允许范围。条件约束中指定的布尔（Boolean）搜寻条件传回的是TRUE时，在数据行中插入或修改的值才算有效。例如，如果我们要限制items数据表的price数据行允许值的可能范围在 $0.01到 $500.00之间，应该使用下面的陈述式：
CREATE TABLE items
(
item_name char(15) NOT NULL,
item_id smallint NOT NULL IDENTITY(1,1),
price smallmoney NULL,
item_desc varchar(30) NOT NULL DEFAULT 'none'
CONSTRAINT PK_item_id PRIMARY KEY (item_id),
CONSTRAINT CK_price CHECK (price >= .01 AND
price <= 500.00)
)
GO
注意，我们在price数据行中允许NULL，且在数据行中有CHECK条件约束。由于SQL Server可以辨别NULL值和其它型别的值，所以尽管有CHECK条件约束，price数据行仍然可允许为NULL值。同时需要注意的是，我们将这个条件约束命名为CK_price。我们之前曾看到，将条件约束命名在稍后即可简单地用T-SQL来删除和重新建立条件约束。例如，把值的范围修改为从 $1.00到 $1000.00之间，可以使用下面的陈述式：
ALTER TABLE items
DROP CONSTRAINT CK_price
GO

ALTER TABLE items
ADD CONSTRAINT CK_price CHECK (price >= 1.00 AND
price <= 1000.00)
GO
第二个ALTER TABLE命令应该与第一次为现存的items数据表新增条件约束时所使用的命令相同。为现存资料表新增CHECK条件约束和新增FOREIGN KEY条件约束所遵守的规则是一样的。所有现存的列将会根据条件约束来检查，如果所有回传的值不是TRUE，则无法将条件约束将新增到数据表中，而且SQL Server将回传错误讯息：指出ALTER TABLE陈述式与CHECK条件约束冲突。如果一定要新增条件约束，使用WITH NOCHECK指定现存的数据列不生效，而将来插入和修改的资料列得以生效。
________________________________________
注意
不建议使用WITH NOCHECK，因为将来可能无法更新不符合条件约束的列。
________________________________________
下面是增加CK_price条件约束时使用WITH NOCHECK的一个例子：
ALTER TABLE items
WITH NOCHECK ADD CONSTRAINT CK_price
CHECK (price >= 1.00 AND price <= 1000.00)
GO
和FOREIGN KEY条件约束一样，在ALTER TABLE中使用CHECK和NOCHECK关键词也

可以控制CHECK条件约束是否生效。您可能想用这种方法插入一个超出指定范围但依然有效的价格。下面的例子先停用CK_price条件约束，然后再使其生效：
ALTER TABLE items
NOCHECK CONSTRAINT CK_price -- 使条件约束无效
GO

-- 在此插入资料列
GO

ALTER TABLE items
CHECK CONSTRAINT CK_price -- 重新启用条件约束
GO
________________________________________
说明
只有CHECK和FOREIGN KEY类型的条件约束可以用这种方式来控制是否生效。
________________________________________
用Enterprise Manager建立和修改条件约束
　
这部分将学习如何用Enterprise Manager设计数据表窗口来建立、修改和删除条件约束，以及在FOREIGN KEY条件约束的情况下，建立数据库图表（在 第15章 介绍了如何建立数据库图表）。使用Enterprise Manager来建立新资料表或编辑现存数据表时，将显示设计数据表窗口。要建立新数据表，在Enterprise Manager的左边窗格中展开服务器和数据库数据夹，在数据表上按右钮，从快捷菜单中选择 新增 / 数据表 。要显示现存数据表的 设计数据表 窗口，首先在左边窗格中找出展开 数据表 ，在右边窗格中的数据表名称上按右钮，然后从快捷菜单中选择 设计数据表 。
允许NULL值
　
要指定数据行中是否允许NULL值，在 设计数据表 窗口中的 是否允许NULL 标题下简单地选取或清除适当的复选框即可。您可以在建立数据表或修改数据表时设定这一选项。关于允许NULL值的规则请参见 第10章 。图16-11显示了本章前面 〈用T-SQL建立和修改数据表〉 我们曾经建立的customer数据表的 设计数据表 窗口。您可以看到mid_init和cust_phone两个数据行允许NULL值，但其它三数据行不允许NULL值。

　

图16-11 customer数据表设计数据表窗口的「是否允许NULL」数据行的设定
UNIQUE
　
1. 要使用Enterprise Manager来建立和修改条件约束，请遵循以下步骤：
在 设计数据表 窗口中，在工具列中选择 数据表索引属性 按钮（ 存盘 按钮右边的按钮），在 属性 对话框中选择 索引/索引键 卷标页。图16-12显示了customer数据表的 属性 对话框中的 索引/索引键 卷标页。
使用下面的命令来建立这张数据表。数据表的SSN数据行包括一条作为丛集索引的UNIQUE条件约束（SQL Server自动替索引命名为UQ_customer_398D8EEE；您现在应可明白为条件约束及索引特别命名的好处）：
CREATE TABLE customer
(
first_name char(20) NOT NULL,
mid_init char(1) NULL,
last_name char(20) NOT NULL,
SSN char(11) NOT NULL UNIQUE CLUSTERED,
cust_phone char(10) NULL
)
GO

　

图16-12 customer数据表的属性对话框中的索引/索引键卷标页
2. 要建立一个新的UNIQUE条件约束，

在 属性 对话框的 索引/索引键 卷标页上按 新增 按钮，选择条件约束使用的数据行名称，键入新的条件约束名称，然后选择 建立成唯一 - UNIQUE 复选框。如果要使它作为数据行的丛集索引，在数据表中选择 建立成丛集 - CLUSTERED 复选框，并指定填满因子。如果不要SQL Server定期地自动计算索引数据，则选取选取方块旁的选项。
3. 您可以使用 属性 对话框修改UNIQUE条件约束。例如，修改条件约束名称、指定要新增到条件约束的数据行、设定丛集索引选项，以及为索引选择填充因子等。（填满因子将在 第17章 详细介绍。）对条件约束进行修改，完成后按一下 关闭 按钮，然后在Enterprise Manager中按 储存 按钮储存修改。
主索引键
　
您可以为一或多个数据行指定一个主索引键（PRIMARY KEY）条件约束。按以下步骤指定一个PRIMARY KEY条件约束：
1. 在 设计数据表 窗口中，按一下某列中的储存格来选择某个数据行，或按住CTRL键，按一下数据行名称左侧的灰色方块以同时选择多个数据行。
2. 在所选的一个数据行上按右钮，在快捷菜单中选择 设定主索引键 。设定为主索引键的数据行左边会出现一把小钥匙。在把SSN数据行设定为主索引键后，显示的窗口如图16-13所示。我们也已经删除了SSN数据行的UNIQUE条件约束，因为并不需要在同一数据行同时具有UNIQUE条件约束和PRIMARY KEY条件约束。


图16-13 在设计数据表窗口中设定PRIMARY KEY条件约束
3. 如果要把PRIMARY KEY条件约束移动到另一数据行，只需要把新数据行设定为主索引键即可。您并不一定要先移除原来的主索引键，SQL Server将为您删除和重新建立PRIMARY KEY索引。您也可以在 属性 窗口中修改PRIMARY KEY索引。按一下工具列的 储存 按钮储存操作后，修改就会生效。
________________________________________
说明
如果修改了包含数据的数据表中的PRIMARY KEY条件约束，重新建立索引可能会花费一定时间。若数据表中包含大量数据，欲对索引作较大的修改，例如变更数据行或丛集状态，最好在数据库的非使用高峰期进行这种操作。
________________________________________
外部索引键
　
要使用Enterprise Manager建立或修改FOREIGN KEY条件约束，可使用 设计数据表 窗口或建立与外部索引键有关联性的数据库图表。最好在建立数据表时（或者至少在数据插入数据表之前）建立外部索引键关联性。下面的例子将解释这个原因。首先，会学习如何使用 设计数据表 来建立FOREIGN KEY条件约束。我们将利用本章前面建立的两个数据库数据表items和inventory来设定外部索引键关键性。我们会重新建立一个有PRIMARY KEY条件约束的item数据表（这是我们

之前使用的），只是这次的item_id数据行没有IDENTITY性质。因为我们需要一个item_id已经被更新的范例，而拥有IDENTITY性质的数据行需要较多的步骤才能进行更新。我们也会重新建立一个没有FOREIGN KEY条件约束的inventory数据表，以方便我们稍后加入FOREIGN KEY条件约束。要建立上述的两个数据表，请详以下的陈述式：
CREATE TABLE items
(
item_name char(15) NOT NULL,
item_id smallint NOT NULL,
price smallmoney NULL,
item_desc varchar(30) NOT NULL DEFAULT 'none',
CONSTRAINT PK_item_id PRIMARY KEY (item_id)
)
GO

CREATE TABLE inventory
(
store_id tinyint NOT NULL,
item_id smallint NOT NULL,
item_quantity tinyint NOT NULL
)
GO

规则对象
　
使用CHECK条件约束的另一种方式是建立规则对象

用T-SQL建立规则对象
　
例如

USE MyDB
GO
CREATE RULE price_rule AS
(@price >= .01 AND @price <= 500.00)
GO
sp_bindrule "price_rule", "items.price", "futureonly"
GO

要解除系结并删除该关联，使用下面的陈述式:

sp_unbindrule "items.price"
GO
DROP RULE price_rule
GO