How to add Default Constraint to existing Columns in SQL Server Table - SQL Server / TSQL Tutorial Part 91

Scenario:

You are working as SQL Server developer. You already has dbo.Customer table with First Name, Last Name, Country Name and Region Columns. The table contains some records as well. You need to provide Default Constraint scripts for Country Name='USA' and Region='North America'.

Solution:

Let's create dbo.Customer table with Default Constraints and insert some sample data first. 

use YourDatabaseName
Go
Create Table dbo.Customer
(
FirstName VARCHAR(50),
LastName VARCHAR(50),
CountryName VARCHAR(50),
Region VARCHAR(50))

Go
--Insert some sample data 
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
Insert into dbo.Customer(FirstName,LastName)
Values ('John','Smith')
go
Insert into dbo.Customer(FirstName,LastName)
Values ('Christy','Ladson')
go


Let's check the data in dbo.Customer table.
How to add Default Constraint to existing SQL Server Table

Now let's alter the table and add Default Constraints by using below statements.

Alter table dbo.Customer
Add Constraint DF_dbo_Customer_CountryName Default  'USA' for CountryName
,Constraint  DF_dbo_Customer_Region default 'North America' for Region



Let's insert couple of records without providing values for Country Name and Region Columns and see if Default Constraint working as expected.

Insert into dbo.Customer(FirstName,LastName)
Values ('Chris','Cook')
go
Insert into dbo.Customer(FirstName,LastName)
Values ('Lisa','L')



Let's check the data in dbo.Customer table again.
How to add Default Constraint to Existing Columns in SQL Server Table

As can be seen in above picture, the Default Constraint worked as expected.


Video Demo : How to add Default Constraint to existing Column in SQL Server Table


No comments:

Post a Comment