SQLServer缺省约束⽰例
缺省约束
缺省约束经过定义列的缺省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
GO
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]
drop table if exists admin
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]
go
Example 2:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABC_Table](
[Type_ID] [int] NOT NULL,
[Name] [nvarchar](100) NULL,
[CreatedBy] [nvarchar](50) NULL CONSTRAINT [DF_ABC_Table_CreatedBy]  DEFAULT ('Admin'),
[CreatedOn] [datetime] NULL CONSTRAINT [DF_ABC_Table_CreatedOn]  DEFAULT (getdate()),
[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]

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。