ALTER TABLE table_constraint (Transact-SQL)ALTER TABLE table_constraint (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database

指定通过使用 ALTER TABLE 添加到表中的 PRIMARY KEY、UNIQUE、FOREIGN KEY、CHECK 约束或 DEFAULT 定义的属性。Specifies the properties of a PRIMARY KEY, UNIQUE, FOREIGN KEY, a CHECK constraint, or a DEFAULT definition added to a table by using ALTER TABLE.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

[ CONSTRAINT constraint_name ]   
{   
    { PRIMARY KEY | UNIQUE }   
        [ CLUSTERED | NONCLUSTERED ]   
        (column [ ASC | DESC ] [ ,...n ] )  
        [ WITH FILLFACTOR = fillfactor   
        [ WITH ( <index_option>[ , ...n ] ) ]  
        [ ON { partition_scheme_name ( partition_column_name ... )  
          | filegroup | "default" } ]   
    | FOREIGN KEY   
        ( column [ ,...n ] )  
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]   
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]   
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]   
        [ NOT FOR REPLICATION ]   
    | CONNECTION
        ( { node_table TO node_table } 
          [ , {node_table TO node_table }]
          [ , ...n ]
        )
        [ ON DELETE { NO ACTION | CASCADE } ]
    | DEFAULT constant_expression FOR column [ WITH VALUES ]   
    | CHECK [ NOT FOR REPLICATION ] ( logical_expression )  
}  

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法,请参阅早期版本文档To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

CONSTRAINTCONSTRAINT
指定 PRIMARY KEY、UNIQUE、FOREIGN KEY 或 CHECK 约束的开始,或者指定 DEFAULT 定义的开始。Specifies the start of a definition for a PRIMARY KEY, UNIQUE, FOREIGN KEY, or CHECK constraint, or a DEFAULT.

constraint_nameconstraint_name
约束的名称。Is the name of the constraint. 除了不能以数字符号 (#) 开头以外,约束名称还必须符合标识符规则。Constraint names must follow the rules for identifiers, except that the name cannot start with a number sign (#). 如果未提供 constraint_name,则将系统生成的名称分配给约束。If constraint_name is not supplied, a system-generated name is assigned to the constraint.

PRIMARY KEYPRIMARY KEY
通过唯一索引对指定的一列或多列强制实体完整性的约束。Is a constraint that enforces entity integrity for a specified column or columns by using a unique index. 对每个表只能创建一个 PRIMARY KEY 约束。Only one PRIMARY KEY constraint can be created for each table.

UNIQUEUNIQUE
通过唯一索引为指定的一列或多列提供实体完整性的约束。Is a constraint that provides entity integrity for a specified column or columns by using a unique index.

CLUSTERED | NONCLUSTEREDCLUSTERED | NONCLUSTERED
指定为 PRIMARY KEY 或 UNIQUE 约束创建聚集或非聚集索引。Specifies that a clustered or nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY 约束默认为 CLUSTERED。PRIMARY KEY constraints default to CLUSTERED. UNIQUE 约束默认为 NONCLUSTERED。UNIQUE constraints default to NONCLUSTERED.

如果表中已存在聚集约束或聚集索引,则不能指定 CLUSTERED。If a clustered constraint or index already exists on a table, CLUSTERED cannot be specified. 如果表中已存在聚集约束或索引,则 PRIMARY KEY 约束默认为 NONCLUSTERED。If a clustered constraint or index already exists on a table, PRIMARY KEY constraints default to NONCLUSTERED.

无法将 ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、xml 或 image 数据类型的列指定为索引的列 。Columns that are of the ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image data types cannot be specified as columns for an index.

columncolumn
新约束中使用的一个列或一组列,使用括号指定。Is a column or list of columns specified in parentheses that are used in a new constraint.

[ ASC | DESC ][ ASC | DESC ]
指定加入到表约束中的一列或多列的排序顺序。Specifies the order in which the column or columns participating in table constraints are sorted. 默认值为 ASC。The default is ASC.

WITH FILLFACTOR =fillfactorWITH FILLFACTOR =fillfactor
指定数据库引擎Database Engine在存储索引数据时使用的每个索引页的填充程度。Specifies how full the 数据库引擎Database Engine should make each index page used to store the index data. 用户指定的 fillfactor 值的范围可以为 1 到 100。User-specified fillfactor values can be from 1 through 100. 如果未指定值,则默认值为 0。If a value is not specified, the default is 0.

重要

将 WITH FILLFACTOR = fillfactor 记录为适用于 PRIMARY KEY 或 UNIQUE 约束的唯一索引选项是为了保持向后兼容,但在未来的版本中将不会以此方式进行记录。Documenting WITH FILLFACTOR = fillfactor as the only index option that applies to PRIMARY KEY or UNIQUE constraints is maintained for backward compatibility, but will not be documented in this manner in future releases. 可在 ALTER TABLE 的 index_option 子句中指定其他索引选项。Other index options can be specified in the index_option clause of ALTER TABLE.

ON { partition_scheme_name ( partition_column_name ) | filegroup| " default " }ON { partition_scheme_name(partition_column_name) | filegroup| " default " }
适用于SQL Server 2008SQL Server 2008 及更高版本。Applies to: SQL Server 2008SQL Server 2008 and later.

指定为约束创建的索引的存储位置。Specifies the storage location of the index created for the constraint. 如果指定了 partition_scheme_name,则将对该索引进行分区,并将分区映射到由 partition_scheme_name 指定的文件组 。If partition_scheme_name is specified, the index is partitioned and the partitions are mapped to the filegroups that are specified by partition_scheme_name. 如果指定了 filegroup,则将在命名文件组内创建索引。If filegroup is specified, the index is created in the named filegroup. 如果指定了 "default" 或者根本没有指定 ON,将在创建表的同一个文件组中创建索引 。If " default " is specified or if ON is not specified at all, the index is created in the same filegroup as the table. 当为 PRIMARY KEY 约束或 UNIQUE 约束添加聚集索引时,如果指定了 ON,则创建聚集索引时将把整个表移动到指定的文件组中。If ON is specified when a clustered index is added for a PRIMARY KEY or UNIQUE constraint, the whole table is moved to the specified filegroup when the clustered index is created.

在此上下文中,default 不是关键字;它是默认文件组的标识符,且必须被隔开,如 ON "default" 或 ON [default] 。In this context, default is not a keyword; it is an identifier for the default filegroup and must be delimited, as in ON " default " or ON [ default ]. 如果指定了“default”,则当前会话的 QUOTED_IDENTIFIER 选项必须为 ON 。If " default " is specified, the QUOTED_IDENTIFIER option must be ON for the current session. 这是默认设置。This is the default setting.

FOREIGN KEY REFERENCESFOREIGN KEY REFERENCES
为列中数据提供引用完整性的约束。Is a constraint that provides referential integrity for the data in the column. FOREIGN KEY 约束要求列中的每个值在引用的表中对应的被引用列中都存在。FOREIGN KEY constraints require that each value in the column exist in the specified column in the referenced table.

referenced_table_namereferenced_table_name
FOREIGN KEY 约束引用的表。Is the table referenced by the FOREIGN KEY constraint.

ref_columnref_column
新 FOREIGN KEY 约束引用的一个列或一组列(置于括号中)。Is a column or list of columns in parentheses referenced by the new FOREIGN KEY constraint.

ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
指定如果已更改的表中的行具有引用关系,并且被引用行已从父表中删除,则对这些行所采取的操作。Specifies what action happens to rows in the table that is altered, if those rows have a referential relationship and the referenced row is deleted from the parent table. 默认值为 NO ACTION。The default is NO ACTION.

NO ACTIONNO ACTION
SQL Server 数据库引擎SQL Server Database Engine将引发错误,并回滚对父表中行的删除操作。The SQL Server 数据库引擎SQL Server Database Engine raises an error and the delete action on the row in the parent table is rolled back.

CASCADECASCADE
如果从父表中删除一行,则将从引用表中删除相应行。Corresponding rows are deleted from the referencing table if that row is deleted from the parent table.

SET NULLSET NULL
如果删除父表中与外键相对应的行,组成外键的所有值都将设置为 NULL。All the values that make up the foreign key are set to NULL when the corresponding row in the parent table is deleted. 若要执行此约束,外键列必须可为空值。For this constraint to execute, the foreign key columns must be nullable.

SET DEFAULTSET DEFAULT
如果删除父表中与外键相对应的行,组成外键的所有值都将设置为其默认值。All the values that comprise the foreign key are set to their default values when the corresponding row in the parent table is deleted. 若要执行此约束,所有外键列都必须有默认定义。For this constraint to execute, all foreign key columns must have default definitions. 如果某个列可为空值,并且未设置显式的默认值,则将使用 NULL 作为该列的隐式默认值。If a column is nullable and there is no explicit default value set, NULL becomes the implicit default value of the column.

如果该表将包含在使用逻辑记录的合并发布中,则不要指定 CASCADE。Do not specify CASCADE if the table will be included in a merge publication that uses logical records. 有关逻辑记录的详细信息,请参阅通过逻辑记录对相关行的更改进行分组For more information about logical records, see Group Changes to Related Rows with Logical Records.

如果被更改的表已有 INSTEAD OF 触发器 ON DELETE,则不能定义 ON DELETE CASCADE。ON DELETE CASCADE cannot be defined if an INSTEAD OF trigger ON DELETE already exists on the table that is being altered.

例如,在 AdventureWorks2012AdventureWorks2012 数据库中,ProductVendor 表与 Vendor 表有引用关系 。For example, in the AdventureWorks2012AdventureWorks2012 database, the ProductVendor table has a referential relationship with the Vendor table. ProductVendor.VendorID 外键引用 Vendor.VendorID 主键 。The ProductVendor.VendorID foreign key references the Vendor.VendorID primary key.

如果对 Vendor 表的某行执行 DELETE 语句,并且为 ProductVendor.VendorID 指定 ON DELETE CASCADE 操作,则 数据库引擎Database Engine 将检查 ProductVendor 表中的一个或多个依赖行 。If a DELETE statement is executed on a row in the Vendor table and an ON DELETE CASCADE action is specified for ProductVendor.VendorID, the 数据库引擎Database Engine checks for one or more dependent rows in the ProductVendor table. 如果存在依赖行,则除了删除 Vendor 表中被引用的行外,还将删除 ProductVendor 表中的依赖行 。If any exist, the dependent rows in the ProductVendor table will be deleted, in addition to the row referenced in the Vendor table.

相反,如果指定了 NO ACTION,并且 ProductVendor 表中至少有一行引用 Vendor 行,则 数据库引擎Database Engine 将引发错误并回滚对 Vendor 行执行的删除操作 。Conversely, if NO ACTION is specified, the 数据库引擎Database Engine raises an error and rolls back the delete action on the Vendor row when there is at least one row in the ProductVendor table that references it.

ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
指定在发生更改的表中,如果行有引用关系且引用的行在父表中被更新,则对这些行采取什么操作。Specifies what action happens to rows in the table altered when those rows have a referential relationship and the referenced row is updated in the parent table. 默认值为 NO ACTION。The default is NO ACTION.

NO ACTIONNO ACTION
数据库引擎Database Engine将引发错误,并回滚对父表中相应行的更新操作。The 数据库引擎Database Engine raises an error, and the update action on the row in the parent table is rolled back.

CASCADECASCADE
如果在父表中更新了一行,则将在引用表中更新相应的行。Corresponding rows are updated in the referencing table when that row is updated in the parent table.

SET NULLSET NULL
如果更新了父表中的相应行,则会将构成外键的所有值设置为 NULL。All the values that make up the foreign key are set to NULL when the corresponding row in the parent table is updated. 若要执行此约束,外键列必须可为空值。For this constraint to execute, the foreign key columns must be nullable.

SET DEFAULTSET DEFAULT
如果更新了父表中的相应行,则会将构成外键的所有值都设置为其默认值。All the values that make up the foreign key are set to their default values when the corresponding row in the parent table is updated. 若要执行此约束,所有外键列都必须有默认定义。For this constraint to execute, all foreign key columns must have default definitions. 如果某个列可为空值,并且未设置显式的默认值,则将使用 NULL 作为该列的隐式默认值。If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column.

如果该表将包含在使用逻辑记录的合并发布中,则不要指定 CASCADE。Do not specify CASCADE if the table will be included in a merge publication that uses logical records. 有关逻辑记录的详细信息,请参阅通过逻辑记录对相关行的更改进行分组For more information about logical records, see Group Changes to Related Rows with Logical Records.

如果要更改的表已存在 INSTEAD OF 触发器 ON UPDATE,则不能定义 ON UPDATE CASCADE、SET NULL 或 SET DEFAULT 操作。ON UPDATE CASCADE, SET NULL, or SET DEFAULT cannot be defined if an INSTEAD OF trigger ON UPDATE already exists on the table that is being altered.

例如,在 AdventureWorks2012AdventureWorks2012 数据库中,ProductVendor 表与 Vendor 表有引用关系 。For example, in the AdventureWorks2012AdventureWorks2012 database, the ProductVendor table has a referential relationship with the Vendor table. ProductVendor.VendorID 外键引用 Vendor.VendorID 主键 。The ProductVendor.VendorID foreign key references the Vendor.VendorID primary key.

如果对 Vendor 表中的某行执行了 UPDATE 语句,并且为 ProductVendor.VendorID 指定了 ON UPDATE CASCADE 操作,则 数据库引擎Database Engine 将检查 ProductVendor 表中是否有一个或多个依赖行 。If an UPDATE statement is executed on a row in the Vendor table and an ON UPDATE CASCADE action is specified for ProductVendor.VendorID, the 数据库引擎Database Engine checks for one or more dependent rows in the ProductVendor table. 如果存在依赖行,那么 ProductVendor 表中的依赖行将随 Vendor 表中的被引用行一同更新 。If any exist, the dependent row in the ProductVendor table will be updated, as well as the row referenced in the Vendor table.

相反,如果指定了 NO ACTION,则当 ProductVendor 表中至少有一行引用了 Vendor 行时,数据库引擎Database Engine 将引发错误,并回滚对 Vendor 行的更新操作 。Conversely, if NO ACTION is specified, the 数据库引擎Database Engine raises an error and rolls back the update action on the Vendor row when there is at least one row in the ProductVendor table that references it.

NOT FOR REPLICATIONNOT FOR REPLICATION
适用于SQL Server 2008SQL Server 2008 及更高版本。Applies to: SQL Server 2008SQL Server 2008 and later.

可以为 FOREIGN KEY 约束和 CHECK 约束指定该参数。Can be specified for FOREIGN KEY constraints and CHECK constraints. 如果为约束指定了此子句,则当复制代理执行插入、更新或删除操作时,将不会强制执行此约束。If this clause is specified for a constraint, the constraint is not enforced when replication agents perform insert, update, or delete operations.

CONNECTION 指定允许连接给定边缘约束的节点表对。CONNECTION Specifies the pair of node tables that the given edge constraint is allowed to connect. ON DELETE 指定删除通过此边缘表中的边缘连接的节点时,边缘表中的行会发生什么情况。ON DELETE specifies what happens to the rows in the edge table, when the nodes which were connected via the edge(s) in this edge table are deleted.

DEFAULTDEFAULT
指定列的默认值。Specifies the default value for the column. DEFAULT 定义可用于为表中现有数据行的新列提供值。DEFAULT definitions can be used to provide values for a new column in the existing rows of data. DEFAULT 定义无法添加到具有 timestamp 数据类型、IDENTITY 属性、现有 DEFAULT 定义或绑定默认值的列。DEFAULT definitions cannot be added to columns that have a timestamp data type, an IDENTITY property, an existing DEFAULT definition, or a bound default. 如果列已有默认值,则必须删除旧默认值后才能添加新默认值。If the column has an existing default, the default must be dropped before the new default can be added. 如果为用户定义类型列指定了默认值,则该类型应当支持从 constant_expression 到用户定义类型的隐式转换。If a default value is specified for a user-defined type column, the type should support an implicit conversion from constant_expression to the user-defined type. 为了与 SQL ServerSQL Server 的早期版本兼容,可以为 DEFAULT 分配约束名称。To maintain compatibility with earlier versions of SQL ServerSQL Server, a constraint name can be assigned to a DEFAULT.

constant_expressionconstant_expression
用作默认列值的文字值、NULL 或系统函数。Is a literal value, a NULL, or a system function that is used as the default column value. 如果 constant_expression 与定义为 MicrosoftMicrosoft .NET Framework.NET Framework 用户定义类型的列结合使用,则该类型的实现必须支持从 constant_expression 到用户定义类型的隐式转换 。If constant_expression is used in conjunction with a column defined to be of a MicrosoftMicrosoft .NET Framework.NET Framework user-defined type, the implementation of the type must support an implicit conversion from the constant_expression to the user-defined type.

FOR columnFOR column
指定与表级 DEFAULT 定义相关联的列。Specifies the column associated with a table-level DEFAULT definition.

WITH VALUESWITH VALUES
添加列和 DEFAULT 约束时,如果列允许为空,那么对于现有行,使用 WITH VALUES 会将新列的值设置为 DEFAULT constant_expression 中给定的值。When adding a column AND a DEFAULT constraint, if the column allows NULLS using WITH VALUES will, for existing rows, set the new column's value to the value given in DEFAULT constant_expression. 如果要添加的列不允许为空,那么对于现有行,列值始终设置为 DEFAULT constant_expression 中给定的值。If the column being added does not allow NULLS, for existing rows, the column's value will always be set to the value given in the DEFAULT constant expression. 自 SQL Server 2012 起,这可能是元数据操作 adding-not-null-columns-as-an-online-operationStarting in SQL Server 2012 this may be a meta data operation adding-not-null-columns-as-an-online-operation. 如果在没有同时添加相关列的情况下使用它,它将不起作用。If this is used when the related column isn't also being added then it has no effect.

CHECKCHECK
一个约束,该约束通过限制可输入一列或多列中的可能值来强制实现域完整性。Is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns.

logical_expressionlogical_expression
用于 CHECK 约束的逻辑表达式,返回 TRUE 或 FALSE。Is a logical expression used in a CHECK constraint and returns TRUE or FALSE. 与 CHECK 约束一起使用的 logical_expression 无法引用其他表,但可以引用同一表中同一行的其他列。logical_expression used with CHECK constraints cannot reference another table but can reference other columns in the same table for the same row. 该表达式不能引用别名数据类型。The expression cannot reference an alias data type.

备注Remarks

当添加 FOREIGN KEY 或 CHECK 约束时,所有现有数据都要进行约束违反验证,除非指定了 WITH NOCHECK 选项。When FOREIGN KEY or CHECK constraints are added, all existing data is verified for constraint violations unless the WITH NOCHECK option is specified. 如果违反了约束,ALTER TABLE 将失败并返回一个错误。If any violations occur, ALTER TABLE fails and an error is returned. 当在现有列上添加新 PRIMARY KEY 或 UNIQUE 约束时,该列中的数据必须唯一。When a new PRIMARY KEY or UNIQUE constraint is added to an existing column, the data in the column or columns must be unique. 如果存在重复值,ALTER TABLE 语句将失败。If duplicate values are found, ALTER TABLE fails. 当添加 PRIMARY KEY 或 UNIQUE 约束时,WITH NOCHECK 选项不起作用。The WITH NOCHECK option has no effect when PRIMARY KEY or UNIQUE constraints are added.

每个 PRIMARY KEY 和 UNIQUE 约束都将生成一个索引。Each PRIMARY KEY and UNIQUE constraint generates an index. UNIQUE 和 PRIMARY KEY 约束的数目不能导致表上非聚集索引的数目大于 999,也不能导致聚集索引的数目大于 1。The number of UNIQUE and PRIMARY KEY constraints cannot cause the number of indexes on the table to exceed 999 nonclustered indexes and 1 clustered index. 外键约束不会自动生成索引。Foreign key constraints do not automatically generate an index. 然而,经常在查询的联接条件中使用外键列,方法是将一个表的外键约束中的列与另一个表中的主键列或唯一键列匹配。However, foreign key columns are frequently used in join criteria in queries by matching the column or columns in the foreign key constraint of one table with the primary or unique key column or columns in the other table. 针对外键列的索引使数据库引擎Database Engine可以在外键表中快速查找相关数据。An index on the foreign key columns enables the 数据库引擎Database Engine to quickly find related data in the foreign key table.

示例Examples

有关示例,请参阅 ALTER TABLE (Transact-SQL)For examples, see ALTER TABLE (Transact-SQL).

另请参阅See Also

ALTER TABLE (Transact-SQL)ALTER TABLE (Transact-SQL)