DROP DEFAULT (Transact-SQL)DROP DEFAULT (Transact-SQL)

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

從目前資料庫移除一或多個使用者自訂的預設值。Removes one or more user-defined defaults from the current database.

重要

DROP DEFAULT 會在 MicrosoftMicrosoft[SQL Server]SQL Server 的下一個版本中移除。DROP DEFAULT will be removed in the next version of MicrosoftMicrosoft[SQL Server]SQL Server. 請勿在新的開發工作中使用 DROP DEFAULT,並規劃修改目前使用 DROP DEFAULT 的應用程式。Do not use DROP DEFAULT in new development work, and plan to modify applications that currently use them. 請改用預設定義,您可以利用 ALTER TABLECREATE TABLE 的 DEFAULT 關鍵字來建立預設定義。Instead, use default definitions that you can create by using the DEFAULT keyword of ALTER TABLE or CREATE TABLE.

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

語法Syntax

DROP DEFAULT [ IF EXISTS ] { [ schema_name . ] default_name } [ ,...n ] [ ; ]  

引數Arguments

IF EXISTSIF EXISTS
適用於[SQL Server]SQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x)目前版本)。Applies to: [SQL Server]SQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version).

只有在預設值已存在時,才能有條件地將其卸除。Conditionally drops the default only if it already exists.

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

default_namedefault_name
這是現有預設值的名稱。Is the name of an existing default. 若要查看存在的預設清單,請執行 sp_helpTo see a list of defaults that exist, execute sp_help. 預設必須符合識別碼的規則。Defaults must comply with the rules for identifiers. 您可以選擇性地指定預設結構描述名稱。Specifying the default schema name is optional.

RemarksRemarks

在卸除預設之前,如果預設目前繫結到資料行或別名資料類型,請執行 sp_unbindefault 來解除預設的繫結。Before dropping a default, unbind the default by executing sp_unbindefault if the default is currently bound to a column or an alias data type.

在從允許 Null 值的資料行中卸除預設值之後,當加入資料列且未明確提供值時,會在這個位置插入 NULL。After a default is dropped from a column that allows for null values, NULL is inserted in that position when rows are added and no value is explicitly supplied. 從 NOT NULL 資料行中卸除預設值之後,當加入資料列且未明確提供值時,會傳回錯誤訊息。After a default is dropped from a NOT NULL column, an error message is returned when rows are added and no value is explicitly supplied. 稍後,會做為一般 INSERT 陳述式行為的一部份而加入這些資料列。These rows are added later as part of the typical INSERT statement behavior.

[權限]Permissions

若要執行 DROP DEFAULT,使用者至少必須有預設值所屬結構描述的 ALTER 權限。To execute DROP DEFAULT, at a minimum, a user must have ALTER permission on the schema to which the default belongs.

範例Examples

A.A. 卸除預設值Dropping a default

如果預設值尚未繫結到資料行或別名資料類型,只能利用 DROP DEFAULT 來卸除它。If a default has not been bound to a column or to an alias data type, it can just be dropped using DROP DEFAULT. 下列範例會移除名稱為 datedflt 的使用者建立預設值。The following example removes the user-created default named datedflt.

USE AdventureWorks2012;  
GO  
IF EXISTS (SELECT name FROM sys.objects  
         WHERE name = 'datedflt'   
            AND type = 'D')  
   DROP DEFAULT datedflt;  
GO  

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,您可以使用下列語法。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x) you can use the following syntax.

DROP DEFAULT IF EXISTS datedflt;  
GO  

B.B. 卸除已繫結到資料行的預設值Dropping a default that has been bound to a column

下列範例會將預設值和相關聯之 EmergencyContactPhone 資料表的 Contact 資料行解除繫結,再卸除名稱為 phonedflt 的預設值。The following example unbinds the default associated with the EmergencyContactPhone column of the Contact table and then drops the default named phonedflt.

USE AdventureWorks2012;  
GO  
   BEGIN   
      EXEC sp_unbindefault 'Person.Contact.Phone'  
      DROP DEFAULT phonedflt  
   END;  
GO  

另請參閱See Also

CREATE DEFAULT (Transact-SQL) CREATE DEFAULT (Transact-SQL)
sp_helptext (Transact-SQL) sp_helptext (Transact-SQL)
sp_help (Transact-SQL) sp_help (Transact-SQL)
sp_unbindefault (Transact-SQL)sp_unbindefault (Transact-SQL)