RSS

DEFAULT Constraint (default values)

29 Oct

Sometimes you want to insert a specific value to this field when the user leaves this field without a value. In other words, the default value will be added to all new records, if no other value is specified.

You can see this by executing the following T-SQL command:-

CREATE TABLE [dbo].[MyTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[PhoneNumber] [varchar](50) NULL,
[Address] [varchar](500) NULL,
[MobileNumber] [varchar](50) NULL,
[City] [varchar](50) NULL,
[Country] [varchar](50) NULL,
[Notes] [varchar](5000) NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_Name]  DEFAULT (‘Normal Customer‘) FOR [Name]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_PhoneNumber]  DEFAULT (‘No Number‘) FOR [PhoneNumber]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_MobileNumber]  DEFAULT (‘No Number‘) FOR [MobileNumber]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_Notes]  DEFAULT (‘No notes available‘) FOR [Notes]
GO

In the previous T-SQL command, we create a table with some default values of some columns. To test and see the result of this code run the following code:-

INSERT dbo.MyTable DEFAULT VALUES

You will see the next result as a new row added by the previous command:-

 
Leave a comment

Posted by on October 29, 2011 in MS SQL Server

 

Tags: , , , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: