SQL server ca88的约束条件【转】

例如: 修改 ALTERTABLE[USER]ALTERcolumn[NAME]varcharnull 新增
ALTERTABLE[USER]ADD[PRICE]numericNULLDEFAULT0
通过更改、添加、除去列和约束,或者通过启用或禁用约束和触发器来更改表的定义。
语法 ALTERTABLEtable {[ALTERCOLUMNcolumn_name {new_data_type[]
[COLLATE] [NULL|NOTNULL] |{ADD|DROP}ROWGUIDCOL} ] |ADD {[]
|column_nameAScomputed_column_expression }[,…n]
|[WITHCHECK|WITHNOCHECK]ADD {}[,…n] |DROP
{[CONSTRAINT]constraint_name |COLUMNcolumn}[,…n]
|{CHECK|NOCHECK}CONSTRAINT {ALL|constraint_name[,…n]}
|{ENABLE|DISABLE}TRIGGER {ALL|trigger_name[,…n]} } ::=
{column_namedata_type} [[DEFAULTconstant_expression][WITHVALUES]
|[IDENTITY[[NOTFORREPLICATION]]] ] [ROWGUIDCOL] [COLLATE]
[][…n] ::= [CONSTRAINTconstraint_name] {[NULL|NOTNULL]
|[{PRIMARYKEY|UNIQUE} [CLUSTERED|NONCLUSTERED]
[WITHFILLFACTOR=fillfactor] [ON{filegroup|DEFAULT}] ]
|[[FOREIGNKEY] REFERENCESref_table[]
[ONDELETE{CASCADE|NOACTION}] [ONUPDATE{CASCADE|NOACTION}]
[NOTFORREPLICATION] ] |CHECK[NOTFORREPLICATION] } ::=
[CONSTRAINTconstraint_name] {[{PRIMARYKEY|UNIQUE}
[CLUSTERED|NONCLUSTERED] {} [WITHFILLFACTOR=fillfactor]
[ON{filegroup|DEFAULT}] ] |FOREIGNKEY [] REFERENCESref_table[]
[ONDELETE{CASCADE|NOACTION}] [ONUPDATE{CASCADE|NOACTION}]
[NOTFORREPLICATION] |DEFAULTconstant_expression
[FORcolumn][WITHVALUES] |CHECK[NOTFORREPLICATION] } 参数 table
是要更改的表的名称。如果表不在当前数据库中或者不属于当前用户所拥有,可以显式指定数据库和所有者。
ALTERCOLUMN
指定要更改给定列。如果兼容级别是65或小于65,将不允许使用ALTERCOLUMN。
要更改的列不能是: 数据类型为text、image、ntext或timestamp的列。
表的ROWGUIDCOL列。 计算列或用于计算列中的列。 被复制列。
用在索引中的列,除非该列数据类型是varchar、nvarchar或varbinary,数据类型没有更改,而且新列大小等于或者大于旧列大小。
用在由CREATESTATISTICS语句创建的统计中的列。首先用DROPSTATISTICS语句删除统计。由查询优化器自动生成的统计会由ALTERCOLUMN自动除去。
用在PRIMARYKEY或[FOREIGNKEY]REFERENCES约束中的列。
用在CHECK或UNIQUE约束中的列,除非用在CHECK或UNIQUE约束中的可变长度列的长度允许更改。
有相关联的默认值的列,除非在不更改数据类型的情况下允许更改列的长度、精度或小数位数。
column_name
是要更改、添加或除去的列的名称。对于新列,如果数据类型为timestamp,column_name可以省略。对于timestamp数据类型的列,如果未指定column_name,将使用名称timestamp。
new_data_type
是要更改的列的新数据类型。要更改的列的new_data_type应符合下列准则:
原来的数据类型必须可以隐式转换为新数据类型。
new_data_type类型不能为timestamp。
对ALTERCOLUMN,ANSI空默认值始终打开;如果没有指定,列将可为空。
对ALTERCOLUMN,ANSI填充始终打开。
如果要更改的列是标识列,new_data_type必须是支持标识属性的数据类型。
将忽略SETARITHABORT的当前设置。ALTERTABLE语句的行为如同ARITHABORT选项为ON时一样。
precision 是指定数据类型的精度。 scale
是指定数据类型的小数位数。有关有效小数位数值的更多信息, COLLATE
为更改列指定新的排序规则。排序规则名称既可以是Windows排序规则名称,也可以是SQL排序规则名称。
COLLATE子句只能用于更改数据类型为char、varchar、text、nchar、nvarchar和ntext的列的排序规则。如果未指定,则此列采用数据库的默认排序规则。
若满足下列条件,则ALTERCOLUMN不能更改排序规则:
检查约束、外键约束或计算列引用了更改列。
在此列上创建了索引、统计或全文索引。更改列的排序规则时,该列上自动创建的统计将除去。
SCHEMABOUND视图或函数引用了此列。 NULL|NOTNULL
指定该列是否可接受空值。不允许空值的列只有在指定了默认值的情况下,才能用ALTERTABLE语句向表中添加。添加到表中的新列要么允许空值,要么必须指定默认值。
如果新列允许空值,而且没有指定默认值,那么新列在表中每一行都包含空值。如果新列允许空值并且指定了新列的默认值,那么可以使用WITHVALUES选项在表中所有现有行的新列中存储默认值。
如果新列不允许空值,那么新列必须具有DEFAULT定义,而且新列的所有现有行中将自动装载该默认值。
可在ALTERCOLUMN语句中指定NULL以使NOTNULL列允许空值,但PRIMARYKEY约束中的列除外。只有列中不包含空值时,ALTERCOLUMN中才可指定NOTNULL。必须将空值更新为非空值后,才允许执行ALTERCOLUMNNOTNULL语句,比如:
UPDATEMyTableSETNullCol=N’some_value’WHERENullColISNULLALTERTABLEMyTableALTERCOLUMNNullCOlNVARCHARNOTNULL
如果ALTERCOLUMN中指定了NULL或NOTNULL,那么必须同时指定new_data_type[]。如果不更改数据类型、精度和小数位数,请指定列的这些值的当前值。
[{ADD|DROP}ROWGUIDCOL]
指定在指定列上添加或除去ROWGUIDCOL属性。ROWGUIDCOL是一个关键字,表示列是行全局唯一标识符列。对于每个表只能指派一个uniqueidentifier列作为ROWGUIDCOL列。ROWGUIDCOL属性只能指派给uniqueidentifier列。
ROWGUIDCOL属性并不强制列中所存储值的唯一性。该属性也不会为插入到表中的新行自动生成值。若要为每列生成唯一值,那么或者在INSERT语句中使用NEWID函数,或者将NEWID函数指定为该列的默认值。
ADD 指定要添加一个或多个列定义、计算列定义或者表约束。
computed_column_expression
是一个定义计算列的值的表达式。计算列是并不物理地存储在表中的虚拟列,该列用表达式计算得出,该表达式使用同一表中的其它列。例如,计算列的定义可以是:costASprice*qty。表达式可以是非计算列的列名、常量、函数、变量,也可以是用一个或多个运算符连接的上述元素的任意组合。表达式不能为子查询。
计算列可用于选择列表、WHERE子句、ORDERBY字句或其它任何可以使用常规表达式的位置,但下列情况除外:
计算列不能用作DEFAULT或FOREIGNKEY约束定义,也不能与NOTNULL约束定义一起使用。但是,如果计算列由具有确定性的表达式定义,并且索引列中允许计算结果的数据类型,则可将该列用作索引中的键列,或用作PRIMARYKEY或UNIQUE约束的一部分。
例如,如果表中有整数列a和b,那么计算列a+b上可建立索引,而计算列a+DATEPART上则不能,因为该值将在后续调用时更改。
计算列不能作为INSERT或UPDATE语句的目标。
说明由于表中计算列所用列中的各行可能有不同的值,所以计算列的每一行可能有不同的值。
n 是表示前面的项可重复n次的占位符。 WITHCHECK|WITHNOCHECK
指定表中的数据是否用新添加的或重新启用的FOREIGNKEY或CHECK约束进行验证。如果没有指定,对于新约束,假定为WITHCHECK,对于重新启用的约束,假定为WITHNOCHECK。
WITHCHECK和WITHNOCHECK子句不能用于PRIMARYKEY和UNIQUE约束。
如果不想用新CHECK或FOREIGNKEY约束对现有数据进行验证,请用WITHNOCHECK,除了个别情况,不建议这样使用。新约束将在以后的所有更新中生效。任何在添加约束时由WITHNOCHECK抑制的约束违规都可能导致将来的更新失败,如果这些更新操作要更新的行中包含不符合约束条件的数据。
查询优化器不考虑用WITHNOCHECK定义的约束。将忽略这些约束,直到使用ALTERTABLEtableCHECKCONSTRAINTALL语句重新启用这些约束为止。
DROP{[CONSTRAINT]constraint_name|COLUMNcolumn_name}
指定从表中删除constraint_name或者column_name。如果兼容级别小于或等于65,将不允许DROPCOLUMN。可以列出多个列或约束。下面的列不能除去:
被复制列。 用在索引中的列。
用在CHECK、FOREIGNKEY、UNIQUE或PRIMARYKEY约束中的列。
有相关联的默认值的列,或绑定到默认对象的列。 绑定到规则的列。
{CHECK|NOCHECK}CONSTRAINT
指定启用或禁用constraint_name。如果禁用,将来插入或更新该列时将不用该约束条件进行验证。此选项只能与FOREIGNKEY和CHECK约束一起使用。
ALL 指定使用NOCHECK选项禁用所有约束,或者使用CHECK选项启用所有约束。
{ENABLE|DISABLE}TRIGGER
指定启用或禁用trigger_name。当一个触发器被禁用时,它对表的定义依然存在;然而,当在表上执行INSERT、UPDATE或DELETE语句时,触发器中的操作将不执行,除非重新启用该触发器。
ALL 指定启用或禁用表中所有的触发器。 trigger_name
指定要启用或禁用的触发器名称。 column_namedata_type
新列的数据类型。data_type可以是任何Microsoft®SQLServer™数据类型或用户定义数据类型。
DEFAULT
是指定列默认值的关键字。DEFAULT定义可用于为表中现有行的新列提供值。DEFAULT定义不能添加到具有timestamp数据类型、IDENTITY属性、现有DEFAULT定义或绑定默认值的列。如果列已有默认值,必须除去旧默认值后才能添加新默认值。为同SQLServer先前版本保持兼容性,向DEFAULT赋予约束名是可能的。
IDENTITY
指定新列是标识列。在表中添加新行时,SQLServer为列提供一个唯一的增量值。标识列通常与PRIMARYKEY约束一起用作表的唯一行标识符。IDENTITY属性可赋予tinyint、smallint、int、bigint、decimal列。对于每个表只能创建一个标识列。DEFAULT关键字和绑定默认值不能用于标识列。要么种子和增量都同时指定,要么都不指定。如果二者都未指定,则取默认值。
Seed 是用于表中所装载的第一行的值。 Increment
是添加到前一行的标识值的增量值。 NOTFORREPLICATION
指定当复制登录向表中插入数据时,不强制IDENTITY属性。也可对约束指定NOTFORREPLICATION。当复制登录向表中插入数据时,不检查约束条件。
CONSTRAINT
指定PRIMARYKEY、UNIQUE、FOREIGNKEY或CHECK约束的开始,或者指定DEFAULT定义的开始。
constrain_name
是新约束。约束的名称必须符合标识符规则,但其名称的首字符不能为#。如果没有提供constraint_name,约束使用系统生成的名称。
PRIMARYKEY
是通过唯一索引对给定的一列或多列强制实体完整性的约束。对每个表只能创建一个PRIMARYKEY约束。
UNIQUE 是通过唯一索引为给定的一列或多列提供实体完整性的约束。
CLUSTERED|NONCLUSTERED
指定为PRIMARYKEY或UNIQUE约束创建聚集或非聚集索引。PRIMARYKEY约束默认为CLUSTERED;UNIQUE约束默认为NONCLUSTERED。
如果表中已存在聚集约束或索引,那么在ALTERTABLE中就不能指定CLUSTERED。如果表中已存在聚集约束或索引,PRIMARYKEY约束默认为NONCLUSTERED。
WITHFILLFACTOR=fillfactor
指定SQLServer存储索引数据时每个索引页的充满程度。用户指定的fillfactor取值范围从1到100。如果没有指定,那么默认值为0。创建索引时,fillfactor值越低,不必分配新空间即可添加的新索引条目的可用空间就越多。
ON{filegroup|DEFAULT}
指定为约束创建的索引的存储位置。如果指定了filegroup,索引将在该文件组内创建。如果指定了DEFAULT,索引将在默认文件组内创建。如果未指定ON,索引将在表所在的文件组内创建。当为PRIMARYKEY或UNIQUE约束添加聚集索引时,如果指定了ON,那么创建聚集索引时整个表都将移到指定的文件组中。
在这里,DEFAULT不是一个关键字。DEFAULT是默认文件组的标识符,必须用符号界定,如ON”DEFAULT”或ON[DEFAULT]。
FOREIGNKEY…REFERENCES
是为列中数据提供引用完整性的约束。FOREIGNKEY约束要求列中的每个值在被引用表的指定列中都存在。
ref_table 是FOREIGNKEY约束所引用的表。 ref_column
是新FOREIGNKEY约束所引用的一列或多列。 ONDELETE{CASCADE|NOACTION}
指定当表中被更改的行具有引用关系,并且该行所引用的行从父表中删除时,要对被更改行采取的操作。默认设置为NOACTION。
如果指定CASCADE,则从父表中删除被引用行时,也将从引用表中删除引用行。如果指定NOACTION,SQLServer将产生一个错误并回滚父表中的行删除操作。
如果表中已存在ONDELETE的INSTEADOF触发器,那么就不能定义ONDELETE的CASCADE操作。
例如,在Northwind数据库中,Orders表和Customers表之间有引用关系。Orders.CustomerID外键引用Customers.CustomerID主键。
如果对Customers表的某行执行DELETE语句,并且为Orders.CustomerID指定ONDELETECASCADE操作,则SQLServer将在Orders表中检查是否有与被删除的行相关的一行或多行。如果存在相关行,那么Orders表中的相关行将随Customers表中的被引用行一同删除。
反之,如果指定NOACTION,若在Orders表中至少有一行引用Customers表中要删除的行,则SQLServer将产生一个错误并回滚Customers表中的删除操作。
ONUPDATE{CASCADE|NOACTION}
指定当表中被更改的行具有引用关系,并且该行所引用的行在父表中更新时,要对被更改行采取的操作。默认设置为NOACTION。
如果指定CASCADE,则在父表中更新被引用行时,也将在引用表中更新引用行。如果指定NOACTION,SQLServer将产生一个错误并回滚父表中的行更新操作。
如果表中已存在ONDELETE的INSTEADOF触发器,那么就不能定义ONDELETE的CASCADE操作。
例如,在Northwind数据库中,Orders表和Customers表之间有引用关系。Orders.CustomerID外键引用Customers.CustomerID主键。
如果对Customers表的某行执行UPDATE语句,并且为Orders.CustomerID指定ONUPDATECASCADE操作,则SQLServer将在Orders表中检查是否有与被更新行相关的一行或多行。如果存在相关行,那么Orders表中的相关行将随Customers表中的被引用行一同更新。
反之,如果指定了NOACTION,若在Orders表中至少存在一行引用Customers表中要更新的行,那么SQLServer将引发一个错误并回滚Customers表中的更新操作。
[ASC|DESC] 指定加入到表约束中的一列或多列的排序次序。默认设置为ASC。
WITHVALUES
指定在添加到现有行的新列中存储DEFAULTconstant_expression中所给定的值。只有在ADD列子句中指定了DEFAULT的情况下,才能使用WITHVALUES。如果要添加的列允许空值且指定了WITHVALUES,那么将在现有行的新列中存储默认值。如果没有指定WITHVALUES且列允许空值,那么将在现有行的新列中存储NULL值。如果新列不允许空值,那么不论是否指定WITHVALUES,都将在现有行的新列中存储默认值。
column[,…n] 是新约束所用的一列或多列。 constant_expression
是用作列的默认值的字面值、NULL或者系统函数。 FORcolumn
指定与表级DEFAULT定义相关联的列。 CHECK
是通过限制可输入到一列或多列中的可能值强制域完整性的约束。
logical_expression
是用于CHECK约束的返回TRUE或FALSE的逻辑表达式。用于CHECK约束的Logical_expression不能引用其它表,但可引用同一表中同一行的其它列。
注释
若要添加新数据行,请使用INSERT语句。若要删除数据行,请使用DELETE或TRUNCATETABLE语句。若要更改现有行中的值,请使用UPDATE语句。
ALTERTABLE语句指定的更改将立即实现。如果这些更改需要修改表中的行,ALTERTABLE将更新这些行。ALTERTABLE将获取表上的架构修改锁,以确保在更改期间其它连接不能引用该表。对表进行的更改将记录于日志中,并且可以完全恢复。影响非常大的表中所有行的更改,比如除去一列或者用默认值添加NOTNULL列,可能需要较长时间才能完成,并会生成大量日志记录。如同影响大量行的INSERT、UPDATE或者DELETE语句一样,这一类ALTERTABLE语句也应小心使用。
如果过程高速缓存中存在引用该表的执行计划,ALTERTABLE会将这些执行计划标记为下次执行时重新编译。
如果ALTERTABLE语句指定更改其它表所引用的列值,那么根据引用表中ONUPDATE或者ONDELETE所指定的操作,将发生以下两个事件之一。
如果在引用表中没有指定值或指定了NOACTION,那么ALTERTABLE语句导致的更改父表中被引用列的操作将回滚,并且SQLServer将引发一个错误。
如果在引用表中指定了CASCADE,那么由ALTERTABLE语句导致的对父表的更改将应用于父表及其相关表。
添加sql_variant列的ALTERTABLE语句会生成下列警告:
Thetotalrowsizefortable’yy’exceedsthemaximumnumberofbytesperrow.Rowsthatexceedthemaximumnumberofbyteswillnotbeadded.
因为sql_variant的最大长度为8016个字节,所以产生该警告。当某sql_variant列所含值接近最大长度时,即会超过行长度的最大字节限制。
ALTERTABLE语句对具有架构绑定视图的表执行时,所受限制与当前在更改具有简单索引的表时所受的限制相同。添加列是允许的。但是,不允许删除或更改参与架构绑定视图的表中的列。如果ALTERTABLE语句要求更改用在架构绑定视图中的列,更改操作将失败,并且SQLServer将引发一条错误信息。
创建引用表的架构绑定视图不会影响在基表上添加或删除触发器。
当除去约束时,作为约束的一部分而创建的索引也将除去。而通过CREATEINDEX创建的索引必须使用DROPINDEX语句来除去。DBCCDBREINDEX语句可用来重建约束定义的索引部分;而不必使用ALTERTABLE先除去再重新添加约束。
必须删除所有基于列的索引和约束后,才能删除列。
添加约束时,所有现有数据都要进行约束违规验证。如果发生违规,ALTERTABLE语句将失败并返回一个错误。
当在现有列上添加新PRIMARYKEY或UNIQUE约束时,该列中的数据必须唯一。如果存在重复值,ALTERTABLE语句将失败。当添加PRIMARYKEY或UNIQUE约束时,WITHNOCHECK选项不起作用。
每个PRIMARYKEY和UNIQUE约束都将生成一个索引。UNIQUE和PRIMARYKEY约束的数目不能导致表上非聚集索引的数目大于249,聚集索引的数目大于1。
如果要添加的列的数据类型为uniqueidentifier,那么该列可以使用NEWID()函数作为默认值,以向表中现有行的新列提供唯一标识符值。
SQLServer在列定义中并不强制以特定的顺序指定DEFAULT、IDENTITY、ROWGUIDCOL或列约束。
ALTERTABLE的ALTERCOLUMN子句并不会在列上绑定或取消绑定任何规则。必须分别使用sp_bindrule或sp_unbindrule来绑定或取消绑定规则。
可将规则绑定到用户定义数据类型。然后CREATETABLE将自动在以该用户定义数据类型定义的列上绑定该规则。当用ALTERCOLUMN更改列数据类型时,并不会取消绑定这些规则。原用户定义数据类型上的规则仍然绑定在该列上。在ALTERCOLUMN更改了列的数据类型之后,随后执行的任何从该用户定义数据类型上取消绑定规则的sp_unbindrule都不会导致从更改了数据类型的列上取消绑定该规则。如果ALTERCOLUMN将列的数据类型更改为绑定了规则的用户定义数据类型,那么绑定到新数据类型的规则不会绑定到该列。
权限
ALTERTABLE权限默认授予表的所有者、sysadmin固定服务器角色成员、db_owner和db_ddladmin固定数据库角色成员且不可转让。
示例 A.更改表以添加新列
下例添加一个允许空值的列,而且没有通过DEFAULT定义提供值。各行的新列中的值将为NULL。
CREATETABLEdoc_exaGOALTERTABLEdoc_exaADDcolumn_bVARCHARNULLGOEXECsp_helpdoc_exaGODROPTABLEdoc_exaGO
B.更改表以除去列 下例修改表以删除一列。
CREATETABLEdoc_exb(column_aINT,column_bVARCHARGOALTERTABLEdoc_exbDROPCOLUMNcolumn_bGOEXECsp_helpdoc_exbGODROPTABLEdoc_exbGO
C.更改表以添加具有约束的列 下例向表中添加具有UNIQUE约束的新列。
CREATETABLEdoc_excGOALTERTABLEdoc_excADDcolumn_bVARCHARNULLCONSTRAINTexb_uniqueUNIQUEGOEXECsp_helpdoc_excGODROPTABLEdoc_excGO
D.更改表以添加未验证的约束
下例向表中的现有列上添加约束。该列中存在一个违反约束的值;因此,利用WITHNOCHECK来防止对现有行验证约束,从而允许该约束的添加。
CREATETABLEdoc_exdGOINSERTINTOdoc_exdVALUESGOALTERTABLEdoc_exdWITHNOCHECKADDCONSTRAINTexd_checkCHECKGOEXECsp_helpdoc_exdGODROPTABLEdoc_exdGO
E.更改表以添加多个带有约束的列
下例向表中添加多个带有约束的新列。第一个新列具有IDENTITY属性;表中每一行的标识列都将具有递增的新值。
CREATETABLEdoc_exe(column_aINTCONSTRAINTcolumn_a_unUNIQUE)GOALTERTABLEdoc_exeADD/*AddaPRIMARYKEYidentitycolumn.*/column_bINTIDENTITYCONSTRAINTcolumn_b_pkPRIMARYKEY,/*Addacolumnreferencinganothercolumninthesametable.*/column_cINTNULLCONSTRAINTcolumn_c_fkREFERENCESdoc_exe,/*Addacolumnwithaconstrainttoenforcethat*//*nonnulldataisinavalidphonenumberformat.*/column_dVARCHARNULLCONSTRAINTcolumn_d_chkCHECK(column_dISNULLORcolumn_dLIKE”[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]”ORcolumn_dLIKE”[ca88,0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]”),/*Addanonnullcolumnwithadefault.*/column_eDECIMALCONSTRAINTcolumn_e_defaultDEFAULT.081GOEXECsp_helpdoc_exeGODROPTABLEdoc_exeGO
F.添加具有默认值的可为空的列
下例添加可为空的、具有DEFAULT定义的列,并使用WITHVALUES为表中的各现有行提供值。如果没有使用WITHVALUES,那么每一行的新列中都将具有NULL值。
ALTERTABLEMyTableADDAddDatesmalldatetimeNULLCONSTRAINTAddDateDfltDEFAULTgetdate()WITHVALUES
G.禁用并重新启用一个约束
下例禁用用于限制可接受的薪水数据的约束。WITHNOCHECKCONSTRAINT与ALTERTABLE一起使用,以禁用该约束并使正常情况下会引起约束违规的插入操作得以执行。WITHCHECKCONSTRAINT重新启用该约束。
CREATETABLEcnst_example(idINTNOTNULL,nameVARCHARNOTNULL,salaryMONEYNOTNULLCONSTRAINTsalary_capCHECK–ValidinsertsINSERTINTOcnst_exampleVALUESINSERTINTOcnst_exampleVALUES–Thisinsertviolatestheconstraint.INSERTINTOcnst_exampleVALUES–Disabletheconstraintandtryagain.ALTERTABLEcnst_exampleNOCHECKCONSTRAINTsalary_capINSERTINTOcnst_exampleVALUES–Reenabletheconstraintandtryanotherinsert,willfail.ALTERTABLEcnst_exampleCHECKCONSTRAINTsalary_capINSERTINTOcnst_exampleVALUES
H.禁用并重新启用触发器
下例使用ALTERTABLE的DISABLETRIGGER选项来禁用触发器,以使正常情况下会违反触发器条件的插入操作得以执行。然后下例使用ENABLETRIGGER重新启用触发器。
CREATETABLEtrig_example,salaryMONEY)go–Createthetrigger.CREATETRIGGERtrig1ONtrig_exampleFORINSERTasIFFROMINSERTEDWHEREsalary>100000)>0BEGINprint”TRIG1Error:youattemptedtoinsertasalary>$100,000″ROLLBACKTRANSACTIONENDGO–Attemptaninsertthatviolatesthetrigger.INSERTINTOtrig_exampleVALUESGO–Disablethetrigger.ALTERTABLEtrig_exampleDISABLETRIGGERtrig1GO–AttemptaninsertthatwouldnormallyviolatethetriggerINSERTINTOtrig_exampleVALUES(2,”ChuckJones”,100001)GO–Re-enablethetrigger.ALTERTABLEtrig_exampleENABLETRIGGERtrig1GO–Attemptaninsertthatviolatesthetrigger.INSERTINTOtrig_exampleVALUESGO

CREATE TABLE

SQLServer – 约束

[ database_name.[ owner ] .| owner.] table_name

一、约束的分类

  在SQLServer中,有3种不同类型的约束。

  1、实体约束

    实体约束是关于行的,比如某一行出现的值就不允许出现在其他行,例如主键。

  2、域约束

    域约束是关于列的,对于所有行,某一列有那些约束,例如CHECK约束。

  3、参照完整性约束

    如果某列的值必须与其他列的值匹配,那就意味着需要一个参照完整性约束,例如外键。

( { < column_definition >

二、约束命名

  在学习约束之前,首先来了解下为约束命名需要注意哪些地方。

  SQLServer在我们不提供名称时,会自动创建名称,但是由系统自动创建的名称并不是特别有用。

  例如,系统生成的主键名称可能是这样的:PK_Employees_145C0A3F。 

  PK代表主键(primary
key),Employees代表在Employees表中,而剩下的“145C0A3F”部分是为了保证唯一性而随机生成的值。只有通过脚本创建才会得到这种值,如果是通过Managerment
Studio创建表,那么就直接是PK_Employees。

  对于系统自动生成的Check约束名称如:CK_Customers_22AA2996。CK代表这是一个Check约束,Customers代表是在Customers表中,后面的22AA2996还是一个随机数。如果一个表中有多个Check约束,则命名可能如下:

  CK_Customers_22AA2996

  CK_Customers_25869641

  CK_Customers_267ABA7A

  如果你需要修改这些约束其中的一个,那么你很难分辨这些约束到底是哪一个。

  因此,为了能够一眼看上去就知道这个约束是用来干什么的,我们应该使用一种简单明了的短语来进行命名。

  例如要确保某一列电话号码格式正确的约束,我们可以使用命名CK_Customers_PhoneNo这样的短语来命名。

  总之命名要做到以下几点:

  1、一致性

  2、通俗易懂

  3、满足以上两个条件的情况下简化名称。

| column_name AS computed_column_expression

三、键约束

| < table_constraint > ::= [ CONSTRAINT constraint_name ] }

1、主键约束

   主键是每行的唯一标识符,仅仅通过它就能准确定位到一行,其中主键列在整个表中不能有重复,必须包含唯一的值(不能为NULL)。由于主键在关系数据库中的重要性,因此它是所有键和约束中最重要的。

   下面来说说主键的创建方式

  1、在创建表的时候创建主键约束。

create table customer
(
    customerId        int identity    not null
        primary key,  --创建主键约束
    CustomerName    nvarchar(30)    not null
);

  怎么样,非常简单吧!

  2、在已存在的表上创建主键约束

  现在假设已经存在了一张表,但是还没有主键约束:

alter table person
    add constraint PK_Employee_Id  --外键名称
    primary key(personId)  --personId 字段名

  alter名称告诉SQLServer如下信息:

  1、添加了一些内容到表中(也可以删除表中的某些内容)

  2、添加了什么内容(一个约束)

  3、对约束的命名(允许以后直接访问约束)

  4、约束的类型(主键约束)

  5、约束应用于哪个列。

  3、复合主键的创建

  如果实在Management
Studio中,创建复合主键,只需要按住Ctrl键,选中两个列,然后设置为主键就OK了,非常简单。下面主要讲述使用T-SQL创建复合主键的方法:

ALTER TABLE 表名 WITH NOCHECK ADD 
CONSTRAINT [PK_表名] PRIMARY KEY NONCLUSTERED 
( 
[字段名1], 
[字段名2] 
) 

  在多对多联系中,常常会有一张表来描述其他两张表的关系,就以此读者和书为例子:

ca88 1😉

ALTER TABLE ReaderAndBook 
ADD 
CONSTRAINT [PK_ReaderAndBook] PRIMARY KEY NONCLUSTERED 
( 
ReaderId, 
BookId 
) 

ca88 2😉

| [ { PRIMARY KEY | UNIQUE } [ ,…n ]

2、外键约束

  外键既能确保数据完整性,也能表现表之间的关系。添加了外键之后,插入引用表的记录要么必须被引用表中被引用列的某条记录匹配,要么外键列的值必须设置为NULL。

  外键和主键不一样,每个表中的外键数目不限制唯一性。在每个表中,每一有-~253个外键。唯一的限制是一个列只能引用一个外键。一个列可以被多个外键引用。

  1、创建表的时候创建外键

ca88 3😉

create table orders
(
    orderId        int identity    not null
        primary key,
    customerId    int                not null
        foreign key references customer(customerId)  --约束类型-外键-引用表(列名)
);

ca88 4😉

  2、在已存在的表中添加一个外键

  假设上面的代码去掉了添加外键行,那么可以书写代码如下:

alter table orders
    add constraint FK_Orders_CustomerId        --添加约束 名称
        foreign key (customerId)    references customer(customerId)    --外键约束,外键列名,被引用列名

  刚添加的约束和之前添加的约束一样生效,如果某行引用customerId不存在,那么就不允许把该行添加到Orders表中。

  3、级联动作

  外键和其他类型键的一个重要区别是:外键是双向的,即不仅是限制子表的值必须存在于父表中,还在每次对父表操作后检查子行(这样避免了孤行)。SQLServer的默认行为是在子行存在时“限制”父行被删除。然而,有时会自动删除任何依赖的记录,而不是防止删除被引用的记录。同样在更新记录时,可能希望依赖的记录自动引用刚刚更新的记录。比较少见的情况是,你可能希望将引用行改变为某个已知的状态。为此,可以选择将依赖行的值设置为NULL或者那个列的默认值。

  这种进行自动删除和自动更新的过程称为级联。这种过程,特别是删除过程,可以经过几层的以来关系(一条记录依赖于另一条记录,而这另一条记录又依赖其他记录)。在SQLServer中实现级联动作需要做的就是修改外键语法-只需要在添加前面加上ON子句。例如:

alter table orders
    add constraint FK_Orders_CustomerId        --添加约束 名称
        foreign key (customerId)    references customer(customerId)    --外键约束,外键列名,被引用列名
        on update     no action    --默认  修改时不级联更新子表
        on delete     cascade      --删除时级联删除依赖行

  当在进行级联删除时,如果一个表级联了另一个表,而另一个表又级联了其他表,这种级联会一直下去,不受限制,这其实是级联的一个危险之处,很容易一个不小心删掉大量数据。

  级联动作除了no action,cascade之外,还有set null和set
default。后两个是在SQLServer2005中引入的,如果要兼容到SQLServer2000的话,要避免使用这两个级联动作。但是他们的才做是非常简单的:如果执行更新而改变了一个父行的值,那么子行的值将被设置为NULL,或者设置为该列的默认值(不管SET
NULL还是SET DEFAULT)。

  4、外键其他方面的考虑

    外键中的之只有相中可能的选择:

    1、在列中填充与被引用表中的相应列相匹配的值。

      通过定义引用列为NOT
NULL,可以使外键完全是必须的(即用户添加数据时必须引用表中必须有相匹配的一行数据)。

    2、不填充任何值,而使该值为NULL。

      允许引用列有NULL值时,用户可以选择不提供值-即使在被引用表没有与NULL值匹配的行,还是允许插入。

)

3、唯一约束

  唯一约束与主键比较相似,共同点在于它们都要求表中指定的列(或者列的组合)上有一个唯一值,区别是唯一约束没有被看作表中记录的唯一标识符(即使你可以按这样的方式使用也有效),而且可以有多个唯一约束(而在每个表中只能有一个主键)。

  一旦建立了唯一约束,那么指定列中的每个值必须是唯一的。如果更新或者插入一条记录在带唯一约束的列上有已经存在的值的记录,SQLServer将抛出错误,拒绝这个记录。

  和主键不同,唯一约束不会自动防止设置一个NULL值,是否允许为NULL由表中相应列的NULL选项的设置决定,但即使确实允许NULL值,一张表中也只能够插入一个NULL值(如果允许多个,那就不叫唯一了)。

  在已存在的表上创建唯一约束:

alter table Account
    add constraint AK_AccountName    --约束名
    unique (Account_Name)    -- 列名

  AK代表替换键(Alternate Key),唯一约束也叫替换键。

  主键和唯一约束的区别:

  •    
    主键约束不允许出现NULL值。任何索引的索引键都不允许包含null值。但唯一约束允许包含NULL值,但唯一约束把两个NULL值当作重复值,所以施加了唯一约束的每一列只允许包含一个NULL值。
  •    
    创建主键时会自动创建聚集索引,除非当前表中已经含有了聚集索引或是创建主键时指定了NONCLUSTERED关键字。
  •    
    创建唯一约束时会自动创建非聚集索引,除非你指定了CLUSTERED关键字并且当前表中还没有聚集索引。
  •     每个表中只能有一个主键,但可以由多个唯一约束。

[ ON { filegroup | DEFAULT } ]

4、CHECK约束  

  CHECK约束约束可以和一个列关联,也可以和一个表关联,因为它们可以检查一个列的值相对于另外一个列的值,只要这些列都在同一个表中以及值是在更新或者插入的同一行中。CHECK约束还可以用于检查列值组合是否满足某一个标准。

  可以像使用where子句一样的规则来定义CHECK约束。CHECK约束条件的示例如下:

目标 SQL
限制Month列为合适的数字 BETWEEN 1 AND 12
正确的SSN格式 LIKE'[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]’
限制为一个快递公司的特定列表 IN(‘UPS’,’Fed Ex’,EMS’)
价格必须为正数 UnitPrice >= 0
引用同一行中的另外一列 ShipDate >= OrderDate

  上面给出的列表只是一小部分,而条件实际上市无限多的。几乎所有可以放到where子句的条件都可以放到该约束中。而且和其他选择(规则和触发器)相比,CHECK约束执行速度更快。

  在已存在的表中添加一个CHECK约束:

alter table Account
    add constraint CN_AccountAge
    check 
    (Account_Age > 18);    -- 插入年龄必须大于18

  如果此时视图添加一条不满足的记录,将报如下错误:

  insert into Account values (22,'洪',17)

消息 547,级别 16,状态 0,第 1 行
INSERT 语句与 CHECK 约束"CN_AccountAge"冲突。该冲突发生于数据库"Nx",表"dbo.Account", column 'Account_Age'。
语句已终止。

[ TEXTIMAGE_ON { filegroup | DEFAULT } ]

5、DEFAULT约束

  和所有约束一样,DEFAULT约束也是表定义的一个组成部分,它定义了当插入的新行对于定义了默认约束的列未提供相应数据时该怎么办。可以定义它为一个字面值(例如,设置默认薪水为0,或者设置字符串列为”UNKNOWN”),或者某个系统值(getdate())。

  对于DEFAULT约束,要了解以下几个特性:

  1、默认值只在insert语句中使用-在update语句和delete语句中被忽略。

  2、如果在insert语句中提供了任意值,那就不使用默认值。

  3、如果没有提供值,那么总是使用默认值。

  值得注意的是,update命令的规则由一个例外,如果显示说明使用默认值就是例外。可以通过使用关键字DEFAULT表示更新的值设置为默认值。

  5.1在创建表时定义DEFAULT约束:

ca88 5😉

create table person
(
    person_id int identity not null
        primary key,
    person_name nvarchar(30) not null
        default '无名氏',
    person_age int not null
)

ca88 6😉

  在执行语句后:

insert into person (person_age) values(24)

  表中被插入一条记录如下:

  ca88 7

   5.2在已存在的表上添加DEFAULT约束:

alter table person
    add constraint CN_DefaultName
    default    '无名氏' for person_name

< column_definition > ::= { column_name data_type }

6、禁用约束

   有时我们想暂时或永久地消除约束。但是SQL
Server并没有提供删除约束的方法。SQL
Server只允许禁用外键约束或CHECK约束,而同时保持约束的完整性。

   禁用一个数据完整性规则通常是因为已经有无效数据了。这样的数据通常分为以下两类:

  1、在创建约束时已经在数据库中的数据

  2、在约束创建以后希望添加的数据

  SQL
Server允许禁用完整性检查一段时间来对例外的无效数据作处理,然后再重新启用完整性(不是物理删除数据完整性约束)。

    注意:不能禁用主键约束或者唯一约束

  6.1、在创建约束时,忽略检查之前的不满足数据

  要添加一个约束,但是有不应用到已存在的数据中,可以再执行Alter
Table语句添加约束时使用WITH NOCHECK选项。

  按照上面创建Check约束的方法,已经Alter
Table时,表中本身已经存在不符合的数据,那么Alter Table操作将被SQL
Server拒绝执行。除非已经存在的所有数据都满足CHECK约束的条件,否则SQL
Server不会执行创建约束的命令。要解决这个问题,我们可以添加WITH NOCHECK。

  我们先新建一个表只有3个字段的表,Id、姓名、年龄,并在里面插入一条不满足要求的数据:

insert into Account values (23,'洪',17)

   然后执行添加约束命令:

alter table Account
    add constraint CN_AccountAge18
    check 
    (Account_Age > 18);    -- 插入年龄必须大于18

   SQL Server报一下错误:

消息 547,级别 16,状态 0,第 1 行
ALTER TABLE 语句与 CHECK 约束"CN_AccountAge18"冲突。该冲突发生于数据库"Nx",表"dbo.Account", column 'Account_Age'。

   这时候我们换一种方式去执行:

alter table Account
    WITH NOCHECK
    add constraint CN_AccountAge18
    check 
    (Account_Age > 18);    -- 插入年龄必须大于18

   以上代码就能够成功执行,并且只有以后添加的数据具备约束,之前添加的不符合条件的数据记录依然存在。

   6.2临时禁用已存在的约束

   当我们需要从另一数据库中导入数据到表中,而表中已建立了约束的时候,可能会存在一些数据和规则不匹配。当然有一个解决方式是先删除约束,添加需要的数据,然后WITH
NOCHECK在添加回去。但是这样做太麻烦了。我们不需要这么做。我们可以采用名为NOCHECK的选项来运行ALTER语句,这样就能够取消需要的约束。

  先来看看上节中创建的这个约束:

alter table Account
    add constraint CN_AccountAge18
    check 
    (Account_Age > 18);    -- 插入年龄必须大于18

   要取消以上约束可以这样来:

Alter Table Account
    NOCHECK
    constraint CN_AccountAge18

  执行命令:

  insert into Account values (25,'取消了约束',17)

  执行成功,成功添加了一行数据。

  留意到又能够向表中插入格式不匹配的数据了。

  这里要说明下,如下知道一个约束是否是启用还是禁用呢?sp_helpconstraint命令,当我们执行sp_helpconstraint的时候,会有一列status_enabled显示该约束的启用状态:

  sp_helpconstraint Account

  ca88 8

   留意到status_enabled列为Disabled说明是禁用的意思。

  当要启用约束时,只需要用将语句中的NO CHECK替换为CHECK就可以了:

Alter Table Account
    CHECK
    constraint CN_AccountAge18

   执行之后,约束又启用了:再来sp_helpconstraint看下:

  ca88 9

   留意到status_enabled列变成了Enabled。

  status_enabled的两种状态如下:

  Enabled:启用;

  Disabled:禁用;

[ COLLATE < collation_name > ]

7、规则和默认值

  规则和默认值的应用要早于CHECK和DEFAULT约束。他们是较老的SQL
Server备用约束的一部分,当然也不是没有优点。自7.0版本之后,MicroSoft列出规则和默认值只是为了向后兼容,而不准备在以后继续支持这个特性。因此对于生成新代码时,应该使用约束。

  规则、默认值与约束的本质区别是:约束是一个表的特征,本身没有存在形式,而规则和默认值是表和自身的实际对象,本身存在。约束是在表定义中定义的,而规则和默认值是单独定义,然后”绑定到”表上。

  规则和默认值的独立对象特性使得它们可以在重用时不用重新定义。实际上,规则和默认值不限于被绑定到表上,它们也可以绑定到数据类型上。

  7.1规则

   规则和CHECK约束非常相似。它们之间的唯一区别是规则每次只能作用于一个列。可以将同一规则分别绑定到一个表中的多个列,但是规则分别作用于每个列,根本不会意识到其他列的存在。像QtyShipped

<=
QtyOrdered这样的约束不适用于规则(它引用多个列),而LIKE([0-9][0-9][0-9])这样的定义适用于规则。

  定义规则:

  下面定义一个规则,这样就可以首先看到区别所在:

CREATE RULE Age18Rule
    AS @Age > 18  

  这里比较的是一个变量,不管被检查的列是什么值,这个值将用于替换@Age。因此在这个示例中,规则所绑定的任何列的值都必须大于18。

  到目前为止,只是创建了一个规则,但这个规则还没对任何表的任何列起作用,要激活这个规则需要使用一个存储过程:sp_bindrule。

  将规则Age18绑定到表person的person_age列:

  EXEC sp_bindrule 'Age18Rule','person.person_age';

  此时,如果我们执行不满足规则的插入操作:

insert into person values ('绑定规则',17)

   将返回如下报错信息:

消息 513,级别 16,状态 0,第 1 行
列的插入或更新与先前的 CREATE RULE 语句所指定的规则发生冲突。该语句已终止。冲突发生于数据库 'Nx',表 'dbo.person',列 'person_age'。
语句已终止。

  很明显,规则已经生效。

  要特别注意的是,在绑定之前,规则与任何表,任何列都没有关系,因此在绑定的时候,第二个参数要加.指定表名与列名(tablename.column)。

  解除绑定规则:

  当我们需要在一个列上解除绑定规则的时候,只要执行sp_unbindrule

  删除刚才绑定的规则:

EXEC sp_unbindrule 'person.person_age';

  这时候,执行刚才的插入操作,就不会报错了。

  删除规则:

  如果希望将规则从数据库中彻底删除,那么可以在表中使用非常熟悉的DROP语法。

DROP RULE <rule name>

  如删除刚才创建的那条规则:

DROP RULE Age18Rule

  7.2默认值

  默认值类似于DEFAULT。实际上默认值-DEFAULT约束的关系与规则-CHECK约束的关系差不多。区别在于它们被追加到表中的方式和对用户自定义数据类型的默认值(是对象,而不是约束)支持。

  定义默认值的语法和定义规则类似:

  CREATE DEFAULT <default_name>
  AS <default value>

  创建默认值:

  因此,假设要为Age定义一个值为0的默认值:

CREATE DEFAULT AgeDefault
AS 0

  绑定默认值:

  同样,如果不绑定到一个对象上,则默认值是不起作用的。要绑定的话,使用存储过程sp_bindefault。

  EXEC sp_bindefault 'AgeDefault','person.person_age';

  要从表中解决默认值的绑定,使用sp_unbindefault:

  sp_unbindefault 'person.person_age';

  删除默认值:

  如果要从数据库中彻底删除一个默认值,则可以使用DROP语法,与删除规则相同:

  DROP DEFAULT AgeDefault

  7.3确定哪个表和数据类型使用给定的规则或默认值

  如果希望删除或者修改规则或默认值。那么您可以先看看哪些表和数据类型在使用它们。SQL
Server还是采用系统存储过程解决这个问题。这个存储过程是sp_depends。其语法如下所示:

  EXEC sp_depends <object name>

  sp_depends提供了依赖于你所查询对象的所有对象列表。

[ [ DEFAULT constant_expression ]

8、触发器

  触发器也能够用于实现数据完整性,这个内容比较多,新建一篇文章叙述。

| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]

9、如何选择

  经过以上的学习,对于数据完整性,你会发现有很多种可以选择,那么如何挑选合适的约束呢?

限制 优点 缺点
约束

快速

可以引用其他列

在命令执行前发生

遵循ANSI标准

必须对每个表重新定义

不能引用其他表

不能绑定到数据类型

规则

独立的对象

可重用

可以绑定到数据类型

命令执行前发生

稍慢

不能跨列使用

不能引用其他表

实际上只用于向后兼容

默认值

非常灵活

可以引用其他列或其他表

可以通过.NET引用SQL Server之外的其他信息

在命令执行之后发生

系统开销很大

  如果要实现更健壮的逻辑模型以及广泛使用用户自定义数据类型,则一般使用规则和默认值。在这种情况下规则和默认值可以提供很多功能,容易管理,而不用太多的编程开销。

  只有在不能选择约束时使用触发器。和约束一样,他们被附加到表中,而且必须对创建的每个表重新定义。好的方面是触发器几乎可以做数据完整性方面的任何操作。实际上再没有出现外键时,他们常被用作外键的替代品。

  而在其他情况下,应将约束作为数据完整性解决方案的选择。它们执行速度快,而且不难创建。他们的缺点是功能有限(除了外键约束,都不能引用其他表),而且对于通用约束逻辑来说,需要一次次地重新定义。

]

[ ROWGUIDCOL]

[ < column_constraint > ] [ …n ]

< column_constraint > ::= [ CONSTRAINT constraint_name ]

{ [ NULL | NOT NULL ]

| [ { PRIMARY KEY | UNIQUE }

[ CLUSTERED | NONCLUSTERED ]

[ WITH FILLFACTOR = fillfactor ]

[ON {filegroup | DEFAULT} ] ]

]

| [ [ FOREIGN KEY ]

REFERENCES ref_table [ ( ref_column ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

[ NOT FOR REPLICATION ]

]

| CHECK [ NOT FOR REPLICATION ]

( logical_expression )

}

< table_constraint > ::= [ CONSTRAINT constraint_name ]

{ [ { PRIMARY KEY | UNIQUE }

[ CLUSTERED | NONCLUSTERED ]

{ ( column [ ASC | DESC ] [ ,…n ] ) }

[ WITH FILLFACTOR = fillfactor ]

[ ON { filegroup | DEFAULT } ]

]

| FOREIGN KEY

[ ( column [ ,…n ] ) ]

REFERENCES ref_table [ ( ref_column [ ,…n ] ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

[ NOT FOR REPLICATION ]

| CHECK [ NOT FOR REPLICATION ]

( search_conditions )

}

相关文章