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

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

指定 PRIMARY KEY、FOREIGN KEY、UNIQUE 或 CHECK 條件約束的屬性,而這些條件約束是使用 ALTER TABLE 新增至資料表之新資料行定義的一部分。Specifies the properties of a PRIMARY KEY, FOREIGN KEY, UNIQUE, or CHECK constraint that is part of a new column definition added to a table by using ALTER TABLE.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

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

引數Arguments

CONSTRAINTCONSTRAINT
指定開始定義 PRIMARY KEY、UNIQUE、FOREIGN KEY 或 CHECK 條件約束。Specifies the start of the definition for a PRIMARY KEY, UNIQUE, FOREIGN KEY, or CHECK constraint.

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.

NULL | NOT NULLNULL | NOT NULL
指定資料行是否接受 Null 值。Specifies whether the column can accept null values. 不允許 NULL 的資料行必須指定了預設值,才能夠加入。Columns that do not allow null values can be added only if they have a default specified. 如果新資料行允許 Null 值,且未指定預設值,資料表每個資料列的新資料行都會包含 NULL。If the new column allows null values and no default is specified, the new column contains NULL for each row in the table. 如果新資料行允許 Null 值,且加入了預設定義,就可以利用 WITH VALUE 選項,將預設值儲存在資料表每個現有資料列的新資料行中。If the new column allows null values and a default definition is added with the new column, the WITH VALUES option can be used to store the default value in the new column for each existing row in the table.

如果新資料行不允許 Null 值,就必須隨著新資料行加入 DEFAULT 定義。If the new column does not allow null values, a DEFAULT definition must be added with the new column. 這個新資料行會在現有資料列的各個新資料行中,自動載入預設值。The new column automatically loads with the default value in the new columns in each existing row.

當加入資料行需要實際變更資料表的資料列時,例如,在每個資料列中加入 DEFAULT 值,執行 ALTER TABLE 會保留資料表的鎖定。When the addition of a column requires physical changes to the data rows of a table, such as adding DEFAULT values to each row, locks are held on the table while ALTER TABLE runs. 這會影響在鎖定就緒時,變更資料表內容的能力。This affects the ability to change the content of the table while the lock is in place. 相對地,加入允許 Null 值且未指定預設值的資料行,只是一項中繼資料作業,不含任何鎖定。In contrast, adding a column that allows null values and does not specify a default value is a metadata operation only, and involves no locks.

當您使用 CREATE TABLE 或 ALTER TABLE 時,資料庫和工作階段設定值會影響且可能會覆寫資料行定義所用之資料類型的 Null 屬性。When you use CREATE TABLE or ALTER TABLE, database and session settings influence and possibly override the nullability of the data type that is used in a column definition. 我們建議您一律將非計算資料行明確定義為 NULL 或 NOT NULL,如果您採用使用者自訂資料類型,建議您允許資料行使用資料類型的預設 Null 屬性。We recommend that you always explicitly define noncomputed columns as NULL or NOT NULL or, if you use a user-defined data type, that you allow the column to use the default nullability of the data type. 如需詳細資訊,請參閱 CREATE TABLE (Transact-SQL)For more information, see CREATE TABLE (Transact-SQL).

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.

屬於 ntexttextvarchar(max)nvarchar(max)varbinary(max)xmlimage 資料類型的資料行無法指定為索引的資料行。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.

WITH FILLFACTOR = fillfactorWITH FILLFACTOR =fillfactor
指定用來儲存索引資料的每個索引頁面,Database EngineDatabase Engine 所應加以填滿的程度。Specifies how full the Database EngineDatabase Engine should make each index page used to store the index data. 使用者指定的填滿因數可以是從 1 到 100 的值。User-specified fill factor 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 " } 適用於SQL Server 2008SQL Server 2008 及更新版本。ON { partition_scheme_name(partition_column_name) | filegroup | " default " } 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 不是關鍵字。In this context, default, is not a keyword. 它是預設檔案群組的識別碼,必須加以分隔,如 ON " default " 或 ON [ default ]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. 如需詳細資訊,請參閱 SET QUOTED_IDENTIFIER (Transact-SQL)For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

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.

schema_nameschema_name
這是 FOREIGN KEY 條件約束參考之資料表所屬的結構描述名稱。Is the name of the schema to which the table referenced by the FOREIGN KEY constraint belongs.

referenced_table_namereferenced_table_name
這是 FOREIGN KEY 條件約束所參考的資料表。Is the table referenced by the FOREIGN KEY constraint.

ref_columnref_column
這是新 FOREIGN KEY 條件約束所參考的資料行,用括號括住。Is a column 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 Database EngineSQL Server Database Engine 會產生一則錯誤,且會回復父資料表中之資料列的刪除動作。The SQL Server Database EngineSQL 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. 若要執行這個條件約束,外部索引鍵資料行必須可為 Null。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 的資料行,但沒有設定明確的預設值,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。The 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 EngineDatabase 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 EngineDatabase 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,當 Database EngineDatabase EngineProductVendor資料表中有至少一個資料列參考Vendor 資料列時, 會產生一則錯誤,且會回復 Vendor 資料列的刪除動作。Conversely, if NO ACTION is specified, the Database EngineDatabase 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 EngineDatabase Engine 會產生一則錯誤,且會回復父資料表中之資料列的更新動作。The Database EngineDatabase 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. 若要執行這個條件約束,外部索引鍵資料行必須可為 Null。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 的資料行,但沒有設定明確的預設值,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 EngineDatabase 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 EngineDatabase Engine checks for one or more dependent rows in the ProductVendor table. 如果有任何相依的資料列存在,除了 Vendor 資料表中所參考的資料列,還會更新 ProductVendor 資料表中的相依資料列。If any exist, the dependent row in the ProductVendor table will be updated, in addition to the row referenced in the Vendor table.

相反地,如果指定了 NO ACTION,當 Database EngineDatabase EngineProductVendor 資料表中有至少一個資料列參考 Vendor 資料列時, 會產生一則錯誤,且會回復 Vendor 資料列的更新動作。Conversely, if NO ACTION is specified, the Database EngineDatabase 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.

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 EngineDatabase Engine 快速從外部索引鍵資料表中尋找相關資料。An index on the foreign key columns enables the Database EngineDatabase 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)
column_definition (Transact-SQL)column_definition (Transact-SQL)