What is Default Constraint in SQL Server - SQL Server / TSQL Tutorial Part 90

What is Default Constraint in SQL Server :

Default Constraint inserts the default value into a column when you do not provide value for column. 

Let's understand by using below examples.

Scenario:

Let's say you need to create a dbo.Customer table with First Name, Last Name, Country Name and Region. If the user does not provide the values for Country Name and Region Columns you always want to insert Country Name='USA' and for Region='North America'. 

The below script can be used to create Default Constraint for Country Name and Region columns.


USE YourDatabaseName
GO
CREATE TABLE dbo.Customer (
    FirstName VARCHAR(50)
    ,LastName VARCHAR(50)
    ,CountryName VARCHAR(50) DEFAULT 'USA'
    ,Region VARCHAR(50) DEFAULT 'North America'
    )



Now insert couple of records by provide values for all the columns by using below insert statements.

--Insert some sample data by provided CountryName and Region
Insert into dbo.Customer (FirstName,LastName,CountryName,Region)
Values('Aamir','Shahzad','Pakistan','Asia')
go
Insert into dbo.Customer (FirstName,LastName,CountryName,Region)
Values('Sukhjeet','Singh','India','Asia')
go


Now only insert the values in First Name and Last Name columns. As we have created the Default Constraint on Country Name and Region, it should automatically insert CountryName='USA' and Region='North America'.

Insert into dbo.Customer(FirstName,LastName)
Values ('John','Smith')
go

Insert into dbo.Customer(FirstName,LastName)
Values ('Christy','Ladson')



Let's check the data in dbo.Customer table to make sure our Default Constraints are working as expected.

Select * from  dbo.Customer

How to create Default Constraint in SQL Server

When we created Default Constraints, we did not provide any name, let's check what names SQL Server has given them by using sys.objects

--Get the Constraint Names in SQL Server
Select * from sys.objects
where type_desc='DEFAULT_CONSTRAINT'

How to check Default Constraints information in SQL Server

If your company is using some naming standards and you would like to create the default constraint with those standards, you can specify the name while creating them. I am using to use DF_SchemaName_TableName_ColumnName for my constraint name as shown below.

use YourDatabaseName
Go
Create Table dbo.tblCustomer
(
FirstName VARCHAR(50),
LastName VARCHAR(50),
CountryName VARCHAR(50) Constraint DF_dbo_tblCustomer_CountryName Default  'USA' ,
Region VARCHAR(50) Constraint  DF_dbo_tblCustomer_Region default 'North America')


Let's run the query on sys.objects one more time to get Default Constraint name with Table name.

--Get the Constraint Names in SQL Server
Select name,object_name(Parent_object_id) 
as TableName from sys.objects
where type_desc='DEFAULT_CONSTRAINT'

How to create Default Constraint with Name in SQL Server


Video Demo : What is Default Constraint in SQL Server and how to Create Default Constraint






No comments:

Post a Comment