CREATE DEFAULT (Transact-SQL)CREATE DEFAULT (Transact-SQL)

本主題適用於:是SQL Server (從 2008 開始)是Azure SQL Database否Azure SQL 資料倉儲 否平行處理資料倉儲 THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

建立一個稱為預設值的物件。Creates an object called a default. 當繫結到某個資料行或別名資料型別,且在插入作業期間未明確提供任何值時,預設值會指定要插入物件所繫結之資料行 (如果是別名資料型別,則是所有資料行) 的值。When bound to a column or an alias data type, a default specifies a value to be inserted into the column to which the object is bound (or into all columns, in the case of an alias data type), when no value is explicitly supplied during an insert.

重要

未來的 Microsoft SQL Server 版本將移除這項功能。This feature will be removed in a future version of Microsoft SQL Server. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.請改為使用透過 ALTER TABLE 或 CREATE TABLE 的 DEFAULT 關鍵字所建立的預設定義。 Instead, use default definitions created using the DEFAULT keyword of ALTER TABLE or CREATE TABLE.

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

語法Syntax


CREATE DEFAULT [ schema_name . ] default_name   
AS constant_expression [ ; ]  

引數Arguments

schema_nameschema_name
這是預設值所屬的結構描述名稱。Is the name of the schema to which the default belongs.

default_namedefault_name
這是預設值的名稱。Is the name of the default. 預設名稱必須符合識別碼的規則。Default names must conform to the rules for identifiers. 您可以選擇性地指定預設擁有者名稱。Specifying the default owner name is optional.

constant_expressionconstant_expression
這是一個只包含常數值的運算式 (其中不能有任何資料行或其他資料庫物件的名稱)。Is an expression that contains only constant values (it cannot include the names of any columns or other database objects). 除了包含別名資料類型者之外,任何常數、內建函數或數學運算式都可以使用。Any constant, built-in function, or mathematical expression can be used, except those that contain alias data types. 不能使用使用者自訂函數。User-defined functions cannot be used.. 請用單引號 (') 括住字元和日期常數;貨幣、整數和浮點數常數不需要引號。Enclose character and date constants in single quotation marks ('); monetary, integer, and floating-point constants do not require quotation marks. 二進位資料的前面必須是 0x,貨幣資料的前面必須是錢幣符號 ($)。Binary data must be preceded by 0x, and monetary data must be preceded by a dollar sign ($). 預設值必須相容於資料行資料類型。The default value must be compatible with the column data type.

RemarksRemarks

預設名稱只能建立在目前資料庫中。A default name can be created only in the current database. 在資料庫內,必須藉由結構描述,使預設名稱成為唯一。Within a database, default names must be unique by schema. 當建立預設值時,請使用 sp_bindefault,將它繫結到資料行或別名資料類型。When a default is created, use sp_bindefault to bind it to a column or to an alias data type.

如果預設值與所繫結的資料行不相容,當嘗試插入預設值時, SQL ServerSQL Server 會產生一則錯誤訊息。If the default is not compatible with the column to which it is bound, SQL ServerSQL Server generates an error message when trying to insert the default value. 例如,N/A 不能用來作為 numeric 資料行的預設值。For example, N/A cannot be used as a default for a numeric column.

如果預設值對它所繫結的資料行而言太長,就會截斷這個值。If the default value is too long for the column to which it is bound, the value is truncated.

CREATE DEFAULT 陳述式無法在單一批次中,與其他 Transact-SQLTransact-SQL 陳述式結合起來。CREATE DEFAULT statements cannot be combined with other Transact-SQLTransact-SQL statements in a single batch.

您必須先卸除預設值,才能建立同名的新預設值,在卸除預設值之前,您必須先執行 sp_unbindefault 來解除預設值的繫結。A default must be dropped before creating a new one of the same name, and the default must be unbound by executing sp_unbindefault before it is dropped.

如果資料行有預設值和相關聯的規則,預設值便不能違反規則。If a column has both a default and a rule associated with it, the default value must not violate the rule. 永遠不會插入與規則衝突的預設值, SQL ServerSQL Server 每次嘗試插入預設值時,都會產生一則錯誤訊息。A default that conflicts with a rule is never inserted, and SQL ServerSQL Server generates an error message each time it attempts to insert the default.

當繫結到資料行時,在下列情況下,會插入預設值:When bound to a column, a default value is inserted when:

  • 未明確插入值。A value is not explicitly inserted.

  • 搭配 INSERT 使用 DEFAULT VALUES 或 DEFAULT 關鍵字來插入預設值。Either the DEFAULT VALUES or DEFAULT keywords are used with INSERT to insert default values.

    如果建立資料行時指定了 NOT NULL,且並未建立其預設值,當使用者無法在這個資料行中建立項目時,便會產生錯誤訊息。If NOT NULL is specified when creating a column and a default is not created for it, an error message is generated when a user fails to make an entry in that column. 下表說明預設值的存在與資料行定義為 NULL 或 NOT NULL 之間的關聯性。The following table illustrates the relationship between the existence of a default and the definition of a column as NULL or NOT NULL. 資料表中各項目顯示其結果。The entries in the table show the result.

資料行定義Column definition 無項目,無預設值No entry, no default 無項目,有預設值No entry, default 輸入 NULL,無預設值Enter NULL, no default 輸入 NULL,有預設值Enter NULL, default
NULLNULL NULLNULL 預設default NULLNULL NULLNULL
NOT NULLNOT NULL 錯誤Error 預設default errorerror errorerror

若要重新命名預設值,請使用 sp_renameTo rename a default, use sp_rename. 如需預設值的報表,請使用 sp_helpFor a report on a default, use sp_help.

PermissionsPermissions

若要執行 CREATE DEFAULT,使用者至少必須有目前資料庫中的 CREATE DEFAULT 權限,以及正在建立的預設值之結構描述的 ALTER 權限。To execute CREATE DEFAULT, at a minimum, a user must have CREATE DEFAULT permission in the current database and ALTER permission on the schema in which the default is being created.

範例Examples

A.A. 建立簡單字元預設值Creating a simple character default

下列範例會建立一個稱為 unknown 的字元預設值。The following example creates a character default called unknown.

USE AdventureWorks2012;  
GO  
CREATE DEFAULT phonedflt AS 'unknown';  

B.B. 繫結預設值Binding a default

下列範例會繫結 A 範例中所建立的預設值。只有在 Phone 資料表的 Contact 資料行中沒有指定任何項目時,預設值才會生效。The following example binds the default created in example A. The default takes effect only if no entry is specified for the Phone column of the Contact table. 請注意,省略任何項目與在 INSERT 陳述式中明確地陳述 NULL 並不相同。Note that omitting any entry is different from explicitly stating NULL in an INSERT statement.

由於名稱為 phonedflt 的預設值不存在,因此,下列 Transact-SQLTransact-SQL 陳述式會失敗。Because a default named phonedflt does not exist, the following Transact-SQLTransact-SQL statement fails. 這個範例只供說明。This example is for illustration only.

USE AdventureWorks2012;  
GO  
sp_bindefault 'phonedflt', 'Person.PersonPhone.PhoneNumber';  

另請參閱See Also

ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
CREATE RULE (Transact-SQL) CREATE RULE (Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
DROP DEFAULT (Transact-SQL) DROP DEFAULT (Transact-SQL)
DROP RULE (Transact-SQL) DROP RULE (Transact-SQL)
運算式 (Transact-SQL) Expressions (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
sp_bindefault (Transact-SQL) sp_bindefault (Transact-SQL)
sp_help (Transact-SQL) sp_help (Transact-SQL)
sp_helptext (Transact-SQL) sp_helptext (Transact-SQL)
sp_rename (Transact-SQL) sp_rename (Transact-SQL)
sp_unbindefault (Transact-SQL)sp_unbindefault (Transact-SQL)