创建、更改和删除默认值

2025-01-02

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Microsoft Fabric SQL 数据库

在 SQL Server 管理对象（SMO）中，默认约束由 Default 对象表示。

TextBody 对象的 Default 属性用于设置要插入的值。可以是常量或返回常量值的 Transact-SQL 语句，例如 GETDATE（）。不能通过使用 TextBody 方法修改 Alter 属性。相反，必须删除并重新创建 Default 对象。

示例

若要使用所提供的任何代码示例，您必须选择创建应用程序所需的编程环境、编程模板和编程语言。有关详细信息，请参阅在 Visual Studio .NET 中创建 Visual C# SMO 项目。

在 Visual Basic 中创建、更改和删除默认值

此代码示例演示如何创建一个简单文本的默认值，另一个默认值是 Transact-SQL 语句。必须使用 BindToColumn 方法将默认值附加到列，然后使用 UnbindFromColumn 方法将其分离。

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks2022 database.
Dim db As Database
db = srv.Databases("AdventureWorks2022")
'Define a Default object variable by supplying the parent database and the default name 
'in the constructor.
Dim def As [Default]
def = New [Default](db, "Test_Default2")
'Set the TextHeader and TextBody properties that define the default.
def.TextHeader = "CREATE DEFAULT [Test_Default2] AS"
def.TextBody = "GetDate()"
'Create the default on the instance of SQL Server.
def.Create()
'Declare a Column object variable and reference a column in the AdventureWorks2022 database.
Dim col As Column
col = db.Tables("SpecialOffer", "Sales").Columns("StartDate")
'Bind the default to the column.
def.BindToColumn("SpecialOffer", "StartDate", "Sales")
'Unbind the default from the column and remove it from the database.
def.UnbindFromColumn("SpecialOffer", "StartDate", "Sales")
def.Drop()

在 Visual C# 中创建、更改和删除默认值

{  
  
          Server srv = new Server();  
  
            //Reference the AdventureWorks2022 database.   
            Database  db = srv.Databases["AdventureWorks2022"];  
  
            //Define a Default object variable by supplying the parent database and the default name   
            //in the constructor.   
            Default def = new Default(db, "Test_Default2");  
  
            //Set the TextHeader and TextBody properties that define the default.   
            def.TextHeader = "CREATE DEFAULT [Test_Default2] AS";  
            def.TextBody = "GetDate()";  
  
            //Create the default on the instance of SQL Server.   
            def.Create();  
  
            //Bind the default to a column in a table in AdventureWorks2022  
            def.BindToColumn("SpecialOffer", "StartDate", "Sales");  
  
            //Unbind the default from the column and remove it from the database.   
            def.UnbindFromColumn("SpecialOffer", "StartDate", "Sales");  
            def.Drop();  
        }

在 PowerShell 中创建、更改和删除默认值

# Set the path context to the local, default instance of SQL Server and get a reference to AdventureWorks2022  
CD \sql\localhost\default\databases  
$db = get-item AdventureWorks2022  
  
#Define a Default object variable by supplying the parent database and the default name in the constructor.  
$def = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Default `  
-argumentlist $db, "Test_Default2"  
  
#Set the TextHeader and TextBody properties that define the default.   
$def.TextHeader = "CREATE DEFAULT [Test_Default2] AS"  
$def.TextBody = "GetDate()"  
  
#Create the default on the instance of SQL Server.   
$def.Create()  
  
#Bind the default to the column.   
$def.BindToColumn("SpecialOffer", "StartDate", "Sales")  
  
#Unbind the default from the column and remove it from the database.   
$def.UnbindFromColumn("SpecialOffer", "StartDate", "Sales")  
$def.Drop()

另请参阅

Default