SQL 新增/修改 表字段列的类型等
16lz
2022-01-19
例如:
修改(列名前要有column关键字)
ALTERTABLE[USER]ALTERcolumn[NAME]varchar(35)null
新增
ALTERTABLE[USER]ADD[PRICE]numeric(18,8)NULLDEFAULT0
通过更改、添加、除去列和约束，或者通过启用或禁用约束和触发器来更改表的定义。
语法
ALTERTABLEtable
{[ALTERCOLUMNcolumn_name
{new_data_type[(precision[,scale])]
[COLLATE<collation_name>]
[NULL|NOTNULL]
|{ADD|DROP}ROWGUIDCOL}
]
|ADD
{[<column_definition>]
|column_nameAScomputed_column_expression
}[,...n]
|[WITHCHECK|WITHNOCHECK]ADD
{<table_constraint>}[,...n]
|DROP
{[CONSTRAINT]constraint_name
|COLUMNcolumn}[,...n]
|{CHECK|NOCHECK}CONSTRAINT
{ALL|constraint_name[,...n]}
|{ENABLE|DISABLE}TRIGGER
{ALL|trigger_name[,...n]}
}
<column_definition>::=
{column_namedata_type}
[[DEFAULTconstant_expression][WITHVALUES]
|[IDENTITY[(seed,increment)[NOTFORREPLICATION]]]
]
[ROWGUIDCOL]
[COLLATE<collation_name>]
[<column_constraint>][...n]
<column_constraint>::=
[CONSTRAINTconstraint_name]
{[NULL|NOTNULL]
|[{PRIMARYKEY|UNIQUE}
[CLUSTERED|NONCLUSTERED]
[WITHFILLFACTOR=fillfactor]
[ON{filegroup|DEFAULT}]
]
|[[FOREIGNKEY]
REFERENCESref_table[(ref_column)]
[ONDELETE{CASCADE|NOACTION}]
[ONUPDATE{CASCADE|NOACTION}]
[NOTFORREPLICATION]
]
|CHECK[NOTFORREPLICATION]
(logical_expression)
}
<table_constraint>::=
[CONSTRAINTconstraint_name]
{[{PRIMARYKEY|UNIQUE}
[CLUSTERED|NONCLUSTERED]
{(column[,...n])}
[WITHFILLFACTOR=fillfactor]
[ON{filegroup|DEFAULT}]
]
|FOREIGNKEY
[(column[,...n])]
REFERENCESref_table[(ref_column[,...n])]
[ONDELETE{CASCADE|NOACTION}]
[ONUPDATE{CASCADE|NOACTION}]
[NOTFORREPLICATION]
|DEFAULTconstant_expression
[FORcolumn][WITHVALUES]
|CHECK[NOTFORREPLICATION]
(search_conditions)
}
参数
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<collation_name>
为更改列指定新的排序规则。排序规则名称既可以是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'WHERENullColISNULLALTERTABLEMyTableALTERCOLUMNNullCOlNVARCHAR(20)NOTNULL
如果ALTERCOLUMN中指定了NULL或NOTNULL，那么必须同时指定new_data_type[(precision[,scale])]。如果不更改数据类型、精度和小数位数，请指定列的这些值的当前值。
[{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(dd,GETDATE())上则不能，因为该值将在后续调用时更改。
计算列不能作为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约束中的列。
有相关联的默认值（由DEFAULT关键字定义）的列，或绑定到默认对象的列。
绑定到规则的列。
{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(p,0)或者numeric(p,0)列。对于每个表只能创建一个标识列。DEFAULT关键字和绑定默认值不能用于标识列。要么种子和增量都同时指定，要么都不指定。如果二者都未指定，则取默认值(1,1)。
Seed
是用于表中所装载的第一行的值。
Increment
是添加到前一行的标识值的增量值。
NOTFORREPLICATION
指定当复制登录（如sqlrepl）向表中插入数据时，不强制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语句会生成下列警告：
Thetotalrowsize(xx)fortable'yy'exceedsthemaximumnumberofbytesperrow(8060).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_exa(column_aINT)GOALTERTABLEdoc_exaADDcolumn_bVARCHAR(20)NULLGOEXECsp_helpdoc_exaGODROPTABLEdoc_exaGO
B.更改表以除去列
下例修改表以删除一列。
CREATETABLEdoc_exb(column_aINT,column_bVARCHAR(20)NULL)GOALTERTABLEdoc_exbDROPCOLUMNcolumn_bGOEXECsp_helpdoc_exbGODROPTABLEdoc_exbGO
C.更改表以添加具有约束的列
下例向表中添加具有UNIQUE约束的新列。
CREATETABLEdoc_exc(column_aINT)GOALTERTABLEdoc_excADDcolumn_bVARCHAR(20)NULLCONSTRAINTexb_uniqueUNIQUEGOEXECsp_helpdoc_excGODROPTABLEdoc_excGO
D.更改表以添加未验证的约束
下例向表中的现有列上添加约束。该列中存在一个违反约束的值；因此，利用WITHNOCHECK来防止对现有行验证约束，从而允许该约束的添加。
CREATETABLEdoc_exd(column_aINT)GOINSERTINTOdoc_exdVALUES(-1)GOALTERTABLEdoc_exdWITHNOCHECKADDCONSTRAINTexd_checkCHECK(column_a>1)GOEXECsp_helpdoc_exdGODROPTABLEdoc_exdGO
E.更改表以添加多个带有约束的列
下例向表中添加多个带有约束的新列。第一个新列具有IDENTITY属性；表中每一行的标识列都将具有递增的新值。
CREATETABLEdoc_exe(column_aINTCONSTRAINTcolumn_a_unUNIQUE)GOALTERTABLEdoc_exeADD/*AddaPRIMARYKEYidentitycolumn.*/column_bINTIDENTITYCONSTRAINTcolumn_b_pkPRIMARYKEY,/*Addacolumnreferencinganothercolumninthesametable.*/column_cINTNULLCONSTRAINTcolumn_c_fkREFERENCESdoc_exe(column_a),/*Addacolumnwithaconstrainttoenforcethat*//*nonnulldataisinavalidphonenumberformat.*/column_dVARCHAR(16)NULLCONSTRAINTcolumn_d_chkCHECK(column_dISNULLORcolumn_dLIKE"[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"ORcolumn_dLIKE"([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),/*Addanonnullcolumnwithadefault.*/column_eDECIMAL(3,3)CONSTRAINTcolumn_e_defaultDEFAULT.081GOEXECsp_helpdoc_exeGODROPTABLEdoc_exeGO
F.添加具有默认值的可为空的列
下例添加可为空的、具有DEFAULT定义的列，并使用WITHVALUES为表中的各现有行提供值。如果没有使用WITHVALUES，那么每一行的新列中都将具有NULL值。
ALTERTABLEMyTableADDAddDatesmalldatetimeNULLCONSTRAINTAddDateDfltDEFAULTgetdate()WITHVALUES
G.禁用并重新启用一个约束
下例禁用用于限制可接受的薪水数据的约束。WITHNOCHECKCONSTRAINT与ALTERTABLE一起使用，以禁用该约束并使正常情况下会引起约束违规的插入操作得以执行。WITHCHECKCONSTRAINT重新启用该约束。
CREATETABLEcnst_example(idINTNOTNULL,nameVARCHAR(10)NOTNULL,salaryMONEYNOTNULLCONSTRAINTsalary_capCHECK(salary<100000))--ValidinsertsINSERTINTOcnst_exampleVALUES(1,"JoeBrown",65000)INSERTINTOcnst_exampleVALUES(2,"MarySmith",75000)--Thisinsertviolatestheconstraint.INSERTINTOcnst_exampleVALUES(3,"PatJones",105000)--Disabletheconstraintandtryagain.ALTERTABLEcnst_exampleNOCHECKCONSTRAINTsalary_capINSERTINTOcnst_exampleVALUES(3,"PatJones",105000)--Reenabletheconstraintandtryanotherinsert,willfail.ALTERTABLEcnst_exampleCHECKCONSTRAINTsalary_capINSERTINTOcnst_exampleVALUES(4,"EricJames",110000)
H.禁用并重新启用触发器
下例使用ALTERTABLE的DISABLETRIGGER选项来禁用触发器，以使正常情况下会违反触发器条件的插入操作得以执行。然后下例使用ENABLETRIGGER重新启用触发器。
CREATETABLEtrig_example(idINT,nameVARCHAR(10),salaryMONEY)go--Createthetrigger.CREATETRIGGERtrig1ONtrig_exampleFORINSERTasIF(SELECTCOUNT(*)FROMINSERTEDWHEREsalary>100000)>0BEGINprint"TRIG1Error:youattemptedtoinsertasalary>$100,000"ROLLBACKTRANSACTIONENDGO--Attemptaninsertthatviolatesthetrigger.INSERTINTOtrig_exampleVALUES(1,"PatSmith",100001)GO--Disablethetrigger.ALTERTABLEtrig_exampleDISABLETRIGGERtrig1GO--AttemptaninsertthatwouldnormallyviolatethetriggerINSERTINTOtrig_exampleVALUES(2,"ChuckJones",100001)GO--Re-enablethetrigger.ALTERTABLEtrig_exampleENABLETRIGGERtrig1GO--Attemptaninsertthatviolatesthetrigger.INSERTINTOtrig_exampleVALUES(3,"MaryBooth",100001)GO
修改(列名前要有column关键字)
ALTERTABLE[USER]ALTERcolumn[NAME]varchar(35)null
新增
ALTERTABLE[USER]ADD[PRICE]numeric(18,8)NULLDEFAULT0
通过更改、添加、除去列和约束，或者通过启用或禁用约束和触发器来更改表的定义。
语法
ALTERTABLEtable
{[ALTERCOLUMNcolumn_name
{new_data_type[(precision[,scale])]
[COLLATE<collation_name>]
[NULL|NOTNULL]
|{ADD|DROP}ROWGUIDCOL}
]
|ADD
{[<column_definition>]
|column_nameAScomputed_column_expression
}[,...n]
|[WITHCHECK|WITHNOCHECK]ADD
{<table_constraint>}[,...n]
|DROP
{[CONSTRAINT]constraint_name
|COLUMNcolumn}[,...n]
|{CHECK|NOCHECK}CONSTRAINT
{ALL|constraint_name[,...n]}
|{ENABLE|DISABLE}TRIGGER
{ALL|trigger_name[,...n]}
}
<column_definition>::=
{column_namedata_type}
[[DEFAULTconstant_expression][WITHVALUES]
|[IDENTITY[(seed,increment)[NOTFORREPLICATION]]]
]
[ROWGUIDCOL]
[COLLATE<collation_name>]
[<column_constraint>][...n]
<column_constraint>::=
[CONSTRAINTconstraint_name]
{[NULL|NOTNULL]
|[{PRIMARYKEY|UNIQUE}
[CLUSTERED|NONCLUSTERED]
[WITHFILLFACTOR=fillfactor]
[ON{filegroup|DEFAULT}]
]
|[[FOREIGNKEY]
REFERENCESref_table[(ref_column)]
[ONDELETE{CASCADE|NOACTION}]
[ONUPDATE{CASCADE|NOACTION}]
[NOTFORREPLICATION]
]
|CHECK[NOTFORREPLICATION]
(logical_expression)
}
<table_constraint>::=
[CONSTRAINTconstraint_name]
{[{PRIMARYKEY|UNIQUE}
[CLUSTERED|NONCLUSTERED]
{(column[,...n])}
[WITHFILLFACTOR=fillfactor]
[ON{filegroup|DEFAULT}]
]
|FOREIGNKEY
[(column[,...n])]
REFERENCESref_table[(ref_column[,...n])]
[ONDELETE{CASCADE|NOACTION}]
[ONUPDATE{CASCADE|NOACTION}]
[NOTFORREPLICATION]
|DEFAULTconstant_expression
[FORcolumn][WITHVALUES]
|CHECK[NOTFORREPLICATION]
(search_conditions)
}
参数
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<collation_name>
为更改列指定新的排序规则。排序规则名称既可以是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'WHERENullColISNULLALTERTABLEMyTableALTERCOLUMNNullCOlNVARCHAR(20)NOTNULL
如果ALTERCOLUMN中指定了NULL或NOTNULL，那么必须同时指定new_data_type[(precision[,scale])]。如果不更改数据类型、精度和小数位数，请指定列的这些值的当前值。
[{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(dd,GETDATE())上则不能，因为该值将在后续调用时更改。
计算列不能作为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约束中的列。
有相关联的默认值（由DEFAULT关键字定义）的列，或绑定到默认对象的列。
绑定到规则的列。
{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(p,0)或者numeric(p,0)列。对于每个表只能创建一个标识列。DEFAULT关键字和绑定默认值不能用于标识列。要么种子和增量都同时指定，要么都不指定。如果二者都未指定，则取默认值(1,1)。
Seed
是用于表中所装载的第一行的值。
Increment
是添加到前一行的标识值的增量值。
NOTFORREPLICATION
指定当复制登录（如sqlrepl）向表中插入数据时，不强制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语句会生成下列警告：
Thetotalrowsize(xx)fortable'yy'exceedsthemaximumnumberofbytesperrow(8060).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_exa(column_aINT)GOALTERTABLEdoc_exaADDcolumn_bVARCHAR(20)NULLGOEXECsp_helpdoc_exaGODROPTABLEdoc_exaGO
B.更改表以除去列
下例修改表以删除一列。
CREATETABLEdoc_exb(column_aINT,column_bVARCHAR(20)NULL)GOALTERTABLEdoc_exbDROPCOLUMNcolumn_bGOEXECsp_helpdoc_exbGODROPTABLEdoc_exbGO
C.更改表以添加具有约束的列
下例向表中添加具有UNIQUE约束的新列。
CREATETABLEdoc_exc(column_aINT)GOALTERTABLEdoc_excADDcolumn_bVARCHAR(20)NULLCONSTRAINTexb_uniqueUNIQUEGOEXECsp_helpdoc_excGODROPTABLEdoc_excGO
D.更改表以添加未验证的约束
下例向表中的现有列上添加约束。该列中存在一个违反约束的值；因此，利用WITHNOCHECK来防止对现有行验证约束，从而允许该约束的添加。
CREATETABLEdoc_exd(column_aINT)GOINSERTINTOdoc_exdVALUES(-1)GOALTERTABLEdoc_exdWITHNOCHECKADDCONSTRAINTexd_checkCHECK(column_a>1)GOEXECsp_helpdoc_exdGODROPTABLEdoc_exdGO
E.更改表以添加多个带有约束的列
下例向表中添加多个带有约束的新列。第一个新列具有IDENTITY属性；表中每一行的标识列都将具有递增的新值。
CREATETABLEdoc_exe(column_aINTCONSTRAINTcolumn_a_unUNIQUE)GOALTERTABLEdoc_exeADD/*AddaPRIMARYKEYidentitycolumn.*/column_bINTIDENTITYCONSTRAINTcolumn_b_pkPRIMARYKEY,/*Addacolumnreferencinganothercolumninthesametable.*/column_cINTNULLCONSTRAINTcolumn_c_fkREFERENCESdoc_exe(column_a),/*Addacolumnwithaconstrainttoenforcethat*//*nonnulldataisinavalidphonenumberformat.*/column_dVARCHAR(16)NULLCONSTRAINTcolumn_d_chkCHECK(column_dISNULLORcolumn_dLIKE"[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"ORcolumn_dLIKE"([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),/*Addanonnullcolumnwithadefault.*/column_eDECIMAL(3,3)CONSTRAINTcolumn_e_defaultDEFAULT.081GOEXECsp_helpdoc_exeGODROPTABLEdoc_exeGO
F.添加具有默认值的可为空的列
下例添加可为空的、具有DEFAULT定义的列，并使用WITHVALUES为表中的各现有行提供值。如果没有使用WITHVALUES，那么每一行的新列中都将具有NULL值。
ALTERTABLEMyTableADDAddDatesmalldatetimeNULLCONSTRAINTAddDateDfltDEFAULTgetdate()WITHVALUES
G.禁用并重新启用一个约束
下例禁用用于限制可接受的薪水数据的约束。WITHNOCHECKCONSTRAINT与ALTERTABLE一起使用，以禁用该约束并使正常情况下会引起约束违规的插入操作得以执行。WITHCHECKCONSTRAINT重新启用该约束。
CREATETABLEcnst_example(idINTNOTNULL,nameVARCHAR(10)NOTNULL,salaryMONEYNOTNULLCONSTRAINTsalary_capCHECK(salary<100000))--ValidinsertsINSERTINTOcnst_exampleVALUES(1,"JoeBrown",65000)INSERTINTOcnst_exampleVALUES(2,"MarySmith",75000)--Thisinsertviolatestheconstraint.INSERTINTOcnst_exampleVALUES(3,"PatJones",105000)--Disabletheconstraintandtryagain.ALTERTABLEcnst_exampleNOCHECKCONSTRAINTsalary_capINSERTINTOcnst_exampleVALUES(3,"PatJones",105000)--Reenabletheconstraintandtryanotherinsert,willfail.ALTERTABLEcnst_exampleCHECKCONSTRAINTsalary_capINSERTINTOcnst_exampleVALUES(4,"EricJames",110000)
H.禁用并重新启用触发器
下例使用ALTERTABLE的DISABLETRIGGER选项来禁用触发器，以使正常情况下会违反触发器条件的插入操作得以执行。然后下例使用ENABLETRIGGER重新启用触发器。
CREATETABLEtrig_example(idINT,nameVARCHAR(10),salaryMONEY)go--Createthetrigger.CREATETRIGGERtrig1ONtrig_exampleFORINSERTasIF(SELECTCOUNT(*)FROMINSERTEDWHEREsalary>100000)>0BEGINprint"TRIG1Error:youattemptedtoinsertasalary>$100,000"ROLLBACKTRANSACTIONENDGO--Attemptaninsertthatviolatesthetrigger.INSERTINTOtrig_exampleVALUES(1,"PatSmith",100001)GO--Disablethetrigger.ALTERTABLEtrig_exampleDISABLETRIGGERtrig1GO--AttemptaninsertthatwouldnormallyviolatethetriggerINSERTINTOtrig_exampleVALUES(2,"ChuckJones",100001)GO--Re-enablethetrigger.ALTERTABLEtrig_exampleENABLETRIGGERtrig1GO--Attemptaninsertthatviolatesthetrigger.INSERTINTOtrig_exampleVALUES(3,"MaryBooth",100001)GO
更多相关文章
- Android开发从零开始之java-泛型初步
- 链接器解析多重定义的全局变量
- android Uri获取真实路径转换成File的方法
- Android(安卓)识别SIM卡类型
- PduHeader
- android 判断联网类型
- Android电池信息(Battery information)
- 安卓4.X系统 增加蓝牙接收文件类型
- JS判断终端类型的几种方法