Default Constraint in SQL Server

 The Default constraint is used to provide default value for a column. Default value will be added to all new records if not values are provided.

You can get information about default constraints from sys.default_constraints catalog view.

Example:

Create table Order_Details

(

ID int Not NULL,

First_Name varchar(50) Not NULL,

City varchar(30),

OrderDate date Default GETDATE()

)

 insert into Order_Details values (1,'Bilash','Bangalore',Default)

insert into Order_Details values (2,'Anup','Hyderabad','2019-01-05')

 select * from Order_Details

First record is inserted the default date value.

Add and Drop the Default Constraint

Alter table Order_Details add constraint Order_Details_Default Default 'Delhi' for City

insert into Order_Details values (3,'Indy',Default,Default)

Alter table Order_Details drop constraint Order_Details_Default

Different types of SQL Server Constraints:

• Not Null Constraint
• Check Constraint
• Default Constraint
• Unique Constraint
• Primary Key Constraint
• Foreign Key Constraint

Check Constraint in SQL Server

 Check constraint is used to limit the value that you can put into one column or multiple columns. This will specify a logical expression which will be evaluated every time a row is inserted or corresponding column is modified. 

check constraints help us to enforce data integrity and, in some cases, lead to better execution plans. It is a good idea to use them as long as you can live with the overhead that they introduce during data modification. 

You can get information about check constraints from the sys.check_constraints catalog view .

Example:

It is always best practice to use the constraint name during the creation.

create table dbo.Accounts

(

AccountId int not null identity(1,1),

AccountType varchar(32) not null,

Age int,

constraint CHK_Accounts_AccountType

check (AccountType in ('Checking','Saving','Credit Card')),

constraint CHK_Accounts_Age

check (Age> 18)

)

insert into Accounts values

('Saving',20)

Drop the Check Constraint

To drop a check constraint use the following SQL script.

Alter table Accounts

drop constraint CHK_Accounts_Age;

Add the Check Constraint

ALTER TABLE Accounts

ADD CONSTRAINT CHK_PersonAge CHECK (Age>18)

Different types of SQL Server Constraints:

• Not Null Constraint
• Check Constraint
• Default Constraint
• Unique Constraint
• Primary Key Constraint
• Foreign Key Constraint