SQL Server 預設限制 示例

預設限制

預設限制經過定義列的預設value或應用資料庫的預設value對象綁定表的列，來指定列的預設value。SQL Server 推薦應用預設限制，而不應用定義預設value的方法來指定列的預設value。有關綁定預設限制的竅門請參見“資料完整性”章節。

定義預設限制的語言規則如下：

CONSTRAINT constraint_title

DEFAULT constant_expression [FOR column_title]

例子：

constraint de_order_quantity default 100 for order_quantity

留意：不能在建立表時定義預設限制，隻能向已經建立好的表中添加預設限制。

為既有表增加DEFAULT限制

ALTER TABLE table_name 

ADD CONSTRAINT constraint_name 

DEFAULT default_value 

FOR column_name

Example 1:

go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ABC_Table]') AND type in (N'U'))

DROP TABLE [dbo].[ABC_Table]

go

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[ABC_Table](

    [Type_ID] [int] NOT NULL,

    [Name] [nvarchar](100) NULL,

    [CreatedBy] [nvarchar](50) NULL,

    [CreatedOn] [datetime] NULL ,

    [UpdatedBy] [nvarchar](50) NULL,

    [UpdatedOn] [datetime] NULL,

CONSTRAINT [PK_ABC_Table] PRIMARY KEY CLUSTERED

(

    [Type_ID] ASC

)

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE ABC_Table

ADD CONSTRAINT DF_ABC_Table_CreatedBy

DEFAULT 'Admin' 

FOR [CreatedBy]

go

ALTER TABLE ABC_Table

ADD CONSTRAINT DF_ABC_Table_CreatedOn

DEFAULT (getdate())

FOR [CreatedOn]

Example 2:

SET ANSI_NULLS ON

    [CreatedBy] [nvarchar](50) NULL CONSTRAINT [DF_ABC_Table_CreatedBy]  DEFAULT ('Admin'),

    [CreatedOn] [datetime] NULL CONSTRAINT [DF_ABC_Table_CreatedOn]  DEFAULT (getdate()),

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

​