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' lumn | user_defined_datatype
[", futureonly"]
示例：
USE MyDB
GO
CREATE DEFAULT DF_not_applicable AS 'n/a'
GO
sp_bindefault "DF_not_applicable", "lumnA"
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 "lumnA"
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条件约束，必须先使用DROP CONSTRAINT删除现存条件约束和 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
(
foreign key references用法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条件约束，
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论