【网学网提醒】:网学会员编辑为广大网友搜集整理了:SQL建立与使用默认值、条件约束及规则绩等信息,祝愿广大网友取得需要的信息,参考学习。
SQL建立与使用默认值、条件约束及规则
1、默认值
使用CREATETABLE建立默认值
USEMyDB
CREATETABLEMyTABLE
(
columnAchar(15)NULLDEFAULT'n/a',
columnBintNULLDEFAULT0
)
GO
使用ALTERTABLE命令可以修改数据行中的默认值定义或新增一数据行。若要更改已经定义的默认值数据行,首先必须删除已经有的默认值,然后新增一个新的默认值
如果用CREATETABLE命令建立未命名的默认值,SQLServer将自动替默认值命名。要知道SQLServer为默认值分配了什么名字,以便可以使用T-SQL删除它,您可执行sp_help程序如下:
USEMyDB
GO
sp_helpMyTable
GO
假设我们要把columnA的默认值从n/a改成notapplicable。记住首先必须删除存在的默认值然后再新增一个新的。下述命令即可删除默认值:
ALTERTABLEMyTable
DROPCONSTRAINTDF_MyTable_columnA_2B3F6F97
现在您可以使用下述命令新增一个默认值,这次由我们自己命名:
ALTERTABLEMyTable
ADDCONSTRAINTDF_MyTable_columnA
DEFAULT"Notapplicable"FORcolumnA
GO
当变更已存在的默认值时,所有现存的列将保持原始的默认值。只有新插入的列会使用新的默认值。
用ALTERTABLE命令为已有的数据表新增完整的新数据行,如下所示:
ALTERTABLEMyTable
ADDcolumnCtinyintNOTNULLDEFAULT13
GO
用默认值而不是NULL插入已存在的列,则应该使用DEFAULT中的WITHVALUES选项,如下所示:
ALTERTABLEMyTable
ADDcolumnCtinyintNULLDEFAULT13WITHVALUES
GO
WITHVALUES命令会强行使MyTable中所有现存列的新数据行接受默认值13来代替原来的NULL值。
CREATEDEFAULT和sp_bindefault
如果要在不同的数据表中使用相同的默认值数据行,此方法则较为有效。
使用CREATEDEFAULT的语法如下:
CREATEDEFAULTdefault_nameASconstant_expression
sp_bindefault的语法如下:
sp_bindefault'default_name'table.column|user_defined_datatype
[",futureonly"]
示例:
USEMyDB
GO
CREATEDEFAULTDF_not_applicableAS'n/a'
GO
sp_bindefault"DF_not_applicable","MyTable.columnA"
GO
如果没有指定futureonly,SQLServer将默认值系结到所有已经存在的和新建立的使用者自订类型的数据行上
例如,让我们建立一个名称为area_code的使用者自订型别和名称为DF_area_code的默认值对象,其值为786;然后系结默认值到该使用者自订数据型别上。因为这是新的使用者自订数据型别,因此目前还没有数据行,也就不需要futureonly选项
sp_addtype"area_code","char(3)","NOTNULL"
GO
CREATEDEFAULTDF_area_codeAS786
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的默认值属性将会同时移除。
同样的,只要在不删除预设对象的情形下,您可以随意地解除或系结某个数据行的预设。使用DROPDEFAULT陈述式,可以完全删除一个预设对象
如下所示:
DROPDEFAULTDF_area_code
GO
一旦删除了预设对象,就无法再取回。如果要再次使用,必须使用CREATEDEFAULT重新建立对象。
在默认值储存格中输入字符串,必须放在单引号中,否则储存时会显示SQLServer的错误讯息。
2条件约束
条件约束用于自动维护数据的完整性。举个例子,您可以将一个整数数据行条件约束在1到100的范围内,那么超出此范围的数值则无法被接受
条件约束的五种类型为NOTNULL、UNIQUE、PRIMARYKEY、FOREIGNKEY和CHECK
使用T-SQL建立和修改条件约束
NOTNULL
NOTNULL条件约束相当简单
UNIQUE
UNIQUE条件约束用以确保一个或多个数据行中没有重复的数值
要用T-SQL为数据表建立UNIQUE条件约束,须执行CREATETABLE或ALTERTABLE命令
例如
CREATETABLEcustomer
(
first_namechar(20)NOTNULL,
mid_initchar(1)NULL,
last_namechar(20)NOTNULL,
SSNchar(11)NOTNULLUNIQUECLUSTERED,
cust_phonechar(10)NULL
)
GO
例子
CREATETABLEcustomer
(
first_namechar(20)NOTNULL,
mid_initchar(1)NULL,
last_namechar(20)NOTNULL,
SSNchar(11)NOTNULLUNIQUECLUSTERED,
cust_phonechar(10)NULL,
CONSTRAINTUQ_full_nameUNIQUENONCLUSTERED(first_name,mid_init,last_name)
)
GO
新增数据行条件约束和数据表条件约束的两组命令:
ALTERTABLEcustomer
ADDCONSTRAINTUQ_ssnUNIQUECLUSTERED(SSN)
GO
ALTERTABLEcustomer
ADDCONSTRAINTUQ_full_nameUNIQUENONCLUSTERED(first_name,mid_init,last_name)
GO
要用T-SQL来修改数据行或数据表中已有的UNIQUE条件约束,必须先删除条件约束再重新建立
主索引键
CREATETABLEcustomer
(
first_namechar(20)NOTNULL,
mid_initchar(1)NULL,
last_namechar(20)NOTNULL,
SSNchar(11)PRIMARYKEY,
cust_phonechar(10)NULL
)
GO
另一种可行的方法是以增加CONSTRAINT关键词来命名。使用下面的命令将主索引键命名为PK_SSN:
CREATETABLEcustomer
(
first_namechar(20)NOTNULL,
mid_initchar(1)NULL,
last_namechar(20)NOTNULL,
SSNchar(11)CONSTRAINTPK_SSNPRIMARYKEY,
cust_phonechar(10)NULL
)
GO
您也可以在定义了所有数据表的数据行后,再指定PRIMAR
YKEY条件约束。数据行名称必须在括号中,并在CONSTRAINT后指定,语法如下所示:
CREATETABLEcustomer
(
first_namechar(20)NOTNULL,
mid_initchar(1)NULL,
last_namechar(20)NOTNULL,
SSNchar(11),
cust_phonechar(10)NULL,
CONSTRAINTPK_SSNPRIMARYKEY(SSN)
)
GO
以下为替customer资料表新增PRIMARYKEY
ALTERTABLEcustomer
ADDCONSTRAINTPK_SSNPRIMARYKEYCLUSTERED(SSN)
GO
要删除PRIMARYKEY条件约束,须使用ALTERTABLE命令和DROPCONSTRAINT陈述式
ALTERTABLEcustomer
DROPCONSTRAINTPK_SSN
GO
注意,只有在DROPCONSTRAINT的陈述式中,需要条件约束名称。要使用T-SQL命令修改数据表中现存的PRIMARYKEY条件约束,必须先使用ALTERTABLE...DROPCONSTRAINT删除现存条件约束和ALTERTABLE...ADDCONSTRAINT陈述式来新增条件约束以修改资料表。
外部索引键
首先建立一个数据表,命名为items,其中的item_id数据行具主索引键,如下所示:
CREATETABLEitems
(
item_namechar(15)NOTNULL,
item_idsmallintNOTNULLIDENTITY(1,1),
pricesmallmoneyNULL,
item_descvarchar(30)NOTNULLDEFAULT'none'
CONSTRAINTPK_item_idPRIMARYKEY(item_id)
)
GO
建立inventory的数据表,其中有称为FK_item_id的外部索引键,此外部索引键引用items数据表的item_id
如下所示:
CREATETABLEinventory
(
store_idtinyintNOTNULL,
item_idsmallintNOTNULL,
item_quantitytinyintNOTNULL,
CONSTRAINTFK_item_idFORGIENKEY(item_id)
REFERENCESitems(item_id)
)
GO
下面先删除inventory数据表的旧条件约束,然后新增条件约束的命令:
ALTERTABLEinventory
DROPCONSTRAINTFK_item_id
GO
ALTERTABLEinventory
ADDCONSTRAINTFK_item_idFOREIGNKEY(item_id)
REFERENCESitems(item_id)
GO
当您在现存资料行中新增FOREIGNKEY条件约束,SQLServer会检查数据表中现存的数据列,以确保除NULL值外,外部索引键数据行的值符合参照资料表的PRIMARYKEY条件约束或UNIQUE条件约束。当建立FOREIGNKEY条件约束时,可以使用ALTERTABLE的WITHNOCHECK选项,那么SQLServer就不会去验证现有的值,如下所示:
ALTERTABLEinventory
WITHNOCHECKADDCONSTRAINTFK_item_id
FOREIGNKEY(item_id)
REFERENCESitems(item_id)
GO
WITHNOCHECK选项可避免SQL检查数据表中现存列的值。如此,无论现有值为何,都可以新增条件约束到数据表上。新增条件约束后,外部索引键的完整性也会增强。
________________________________________
注意
使用WITHNOCHECK选项时要小心。因为当您预计更新现存数据,但是现存数据中却包含与条件约束冲突的值,您将无法更新现存数据。
________________________________________
您也可以控制是否启用FOREIGN
KEY条件约束。NOCHECK关键词表示忽略条件约束;CHECK关键词表示条件约束生效。
ALTERTABLEinventory
NOCHECKCONSTRAINTFK_item_id--使条件约束无效
GO
--在此插入您需要的数据列
ALTERTABLEinventory
CHECKCONSTRAINTFK_item_id--重新回复条件约束
GO
________________________________________
说明
尽量不要插入与FOREIGNKEY条件约束冲突的资料行。否则可能导致将来数据表的有冲突的数据列无法被更新。
________________________________________
CHECK
CHECK条件约束用于限制数据行中值的允许范围。条件约束中指定的布尔(Boolean)搜寻条件传回的是TRUE时,在数据行中插入或修改的值才算有效。例如,如果我们要限制items数据表的price数据行允许值的可能范围在$0.01到$500.00之间,应该使用下面的陈述式:
CREATETABLEitems
(
item_namechar(15)NOTNULL,
item_idsmallintNOTNULLIDENTITY(1,1),
pricesmallmoneyNULL,
item_descvarchar(30)NOTNULLDEFAULT'none'
CONSTRAINTPK_item_idPRIMARYKEY(item_id),
CONSTRAINTCK_priceCHECK(price>=.01AND
price<=500.00)
)
GO
注意,我们在price数据行中允许NULL,且在数据行中有CHECK条件约束。由于SQLServer可以辨别NULL值和其它型别的值,所以尽管有CHECK条件约束,price数据行仍然可允许为NULL值。同时需要注意的是,我们将这个条件约束命名为CK_price。我们之前曾看到,将条件约束命名在稍后即可简单地用T-SQL来删除和重新建立条件约束。例如,把值的范围修改为从$1.00到$1000.00之间,可以使用下面的陈述式:
ALTERTABLEitems
DROPCONSTRAINTCK_price
GO
ALTERTABLEitems
ADDCONSTRAINTCK_priceCHECK(price>=1.00AND
price<=1000.00)
GO
第二个ALTERTABLE命令应该与第一次为现存的items数据表新增条件约束时所使用的命令相同。为现存资料表新增CHECK条件约束和新增FOREIGNKEY条件约束所遵守的规则是一样的。所有现存的列将会根据条件约束来检查,如果所有回传的值不是TRUE,则无法将条件约束将新增到数据表中,而且SQLServer将回传错误讯息:指出ALTERTABLE陈述式与CHECK条件约束冲突。如果一定要新增条件约束,使用WITHNOCHECK指定现存的数据列不生效,而将来插入和修改的资料列得以生效。
________________________________________
注意
不建议使用WITHNOCHECK,因为将来可能无法更新不符合条件约束的列。
________________________________________
下面是增加CK_price条件约束时使用WITHNOCHECK的一个例子:
ALTERTABLEitems
WITHNOCHECKADDCONSTRAINTCK_price
CHECK(price>=1.00ANDprice<=1000.00)
GO
和FOREIGNKEY条件约束一样,在ALTERTABLE中使用CHECK和NOCHECK关键词也
可以控制CHECK条件约束是否生效。您可能想用这种方法插入一个超出指定范围但依然有效的价格。下面的例子先停用CK_price条件约束,然后再使其生效:
ALTERTABLEitems
NOCHECKCONSTRAINTCK_price--使条件约束无效
GO
--在此插入资料列
GO
ALTERTABLEitems
CHECKCONSTRAINTCK_price--重新启用条件约束
GO
________________________________________
说明
只有CHECK和FOREIGNKEY类型的条件约束可以用这种方式来控制是否生效。
________________________________________
用EnterpriseManager建立和修改条件约束
这部分将学习如何用EnterpriseManager设计数据表窗口来建立、修改和删除条件约束,以及在FOREIGNKEY条件约束的情况下,建立数据库图表(在第15章介绍了如何建立数据库图表)。使用EnterpriseManager来建立新资料表或编辑现存数据表时,将显示设计数据表窗口。要建立新数据表,在EnterpriseManager的左边窗格中展开服务器和数据库数据夹,在数据表上按右钮,从快捷菜单中选择新增/数据表。要显示现存数据表的设计数据表窗口,首先在左边窗格中找出展开数据表,在右边窗格中的数据表名称上按右钮,然后从快捷菜单中选择设计数据表。
允许NULL值
要指定数据行中是否允许NULL值,在设计数据表窗口中的是否允许NULL标题下简单地选取或清除适当的复选框即可。您可以在建立数据表或修改数据表时设定这一选项。关于允许NULL值的规则请参见第10章。图16-11显示了本章前面〈用T-SQL建立和修改数据表〉我们曾经建立的customer数据表的设计数据表窗口。您可以看到mid_init和cust_phone两个数据行允许NULL值,但其它三数据行不允许NULL值。
图16-11customer数据表设计数据表窗口的「是否允许NULL」数据行的设定
UNIQUE
1.要使用EnterpriseManager来建立和修改条件约束,请遵循以下步骤:
在设计数据表窗口中,在工具列中选择数据表索引属性按钮(存盘按钮右边的按钮),在属性对话框中选择索引/索引键卷标页。图16-12显示了customer数据表的属性对话框中的索引/索引键卷标页。
使用下面的命令来建立这张数据表。数据表的SSN数据行包括一条作为丛集索引的UNIQUE条件约束(SQLServer自动替索引命名为UQ_customer_398D8EEE;您现在应可明白为条件约束及索引特别命名的好处):
CREATETABLEcustomer
(
first_namechar(20)NOTNULL,
mid_initchar(1)NULL,
last_namechar(20)NOTNULL,
SSNchar(11)NOTNULLUNIQUECLUSTERED,
cust_phonechar(10)NULL
)
GO
图16-12customer数据表的属性对话框中的索引/索引键卷标页
2.要建立一个新的UNIQUE条件约束,
在属性对话框的索引/索引键卷标页上按新增按钮,选择条件约束使用的数据行名称,键入新的条件约束名称,然后选择建立成唯一-UNIQUE复选框。如果要使它作为数据行的丛集索引,在数据表中选择建立成丛集-CLUSTERED复选框,并指定填满因子。如果不要SQLServer定期地自动计算索引数据,则选取选取方块旁的选项。
3.您可以使用属性对话框修改UNIQUE条件约束。例如,修改条件约束名称、指定要新增到条件约束的数据行、设定丛集索引选项,以及为索引选择填充因子等。(填满因子将在第17章详细介绍。)对条件约束进行修改,完成后按一下关闭按钮,然后在EnterpriseManager中按储存按钮储存修改。
主索引键
您可以为一或多个数据行指定一个主索引键(PRIMARYKEY)条件约束。按以下步骤指定一个PRIMARYKEY条件约束:
1.在设计数据表窗口中,按一下某列中的储存格来选择某个数据行,或按住CTRL键,按一下数据行名称左侧的灰色方块以同时选择多个数据行。
2.在所选的一个数据行上按右钮,在快捷菜单中选择设定主索引键。设定为主索引键的数据行左边会出现一把小钥匙。在把SSN数据行设定为主索引键后,显示的窗口如图16-13所示。我们也已经删除了SSN数据行的UNIQUE条件约束,因为并不需要在同一数据行同时具有UNIQUE条件约束和PRIMARYKEY条件约束。
图16-13在设计数据表窗口中设定PRIMARYKEY条件约束
3.如果要把PRIMARYKEY条件约束移动到另一数据行,只需要把新数据行设定为主索引键即可。您并不一定要先移除原来的主索引键,SQLServer将为您删除和重新建立PRIMARYKEY索引。您也可以在属性窗口中修改PRIMARYKEY索引。按一下工具列的储存按钮储存操作后,修改就会生效。
________________________________________
说明
如果修改了包含数据的数据表中的PRIMARYKEY条件约束,重新建立索引可能会花费一定时间。若数据表中包含大量数据,欲对索引作较大的修改,例如变更数据行或丛集状态,最好在数据库的非使用高峰期进行这种操作。
________________________________________
外部索引键
要使用EnterpriseManager建立或修改FOREIGNKEY条件约束,可使用设计数据表窗口或建立与外部索引键有关联性的数据库图表。最好在建立数据表时(或者至少在数据插入数据表之前)建立外部索引键关联性。下面的例子将解释这个原因。首先,会学习如何使用设计数据表来建立FOREIGNKEY条件约束。我们将利用本章前面建立的两个数据库数据表items和inventory来设定外部索引键关键性。我们会重新建立一个有PRIMARYKEY条件约束的item数据表(这是我们
之前使用的),只是这次的item_id数据行没有IDENTITY性质。因为我们需要一个item_id已经被更新的范例,而拥有IDENTITY性质的数据行需要较多的步骤才能进行更新。我们也会重新建立一个没有FOREIGNKEY条件约束的inventory数据表,以方便我们稍后加入FOREIGNKEY条件约束。要建立上述的两个数据表,请详以下的陈述式:
CREATETABLEitems
(
item_namechar(15)NOTNULL,
item_idsmallintNOTNULL,
pricesmallmoneyNULL,
item_descvarchar(30)NOTNULLDEFAULT'none',
CONSTRAINTPK_item_idPRIMARYKEY(item_id)
)
GO
CREATETABLEinventory
(
store_idtinyintNOTNULL,
item_idsmallintNOTNULL,
item_quantitytinyintNOTNULL
)
GO
规则对象
使用CHECK条件约束的另一种方式是建立规则对象
用T-SQL建立规则对象
例如
USEMyDB
GO
CREATERULEprice_ruleAS
(@price>=.01AND@price<=500.00)
GO
sp_bindrule"price_rule","items.price","futureonly"
GO
要解除系结并删除该关联,使用下面的陈述式:
sp_unbindrule"items.price"
GO
DROPRULEprice_rule
GO