Tuesday, July 2, 2019

SQL Server - How does Default Constraint Work?

Constraints are the rules and restrictions to apply on the type of data in a table. They specify the limit on the type of data that can be stored in a particular column in a table using constraints and also maintain the data integrity and accuracy in the table. They also ensure the unwanted data can't be inserted into tables.
The basic funda is that A Not null constraint restrict the insertion of null values into a column. 
Default Constraint is another feature of SQL which ensures to provide a default value for the fieldsIt specifies a default value in case an insertion query any value is not specified for this column then the default value will be inserted into the column.

How can we create Check constraints?
In a table one or more column can contain a Default Constraint. We can create Default constraints at two levels -

Column Level Default Constraints - We can define Default constraint with CREATE TABLE statement or in time with table definition.  
For example, the below query creates a table Employee with the column fields EmpId, FirstName, LastName and Age as NOT NULL but MobileNo column with Unique constraint and IsActive column with Default value. Thus, Age column has some specific check constraint to validate the data before inserting into the data table.


----
Column Level Unique Constraints
CREATE
TABLE
TBL_EMPLOYEE
(
EmpId int NOT NULL,
FirstName varchar(25) NOT
NULL,
LastName varchar(25) NOT
NULL,
Age int NOT NULL
CHECK(Age>=18),
MobileNo Bigint
Unique,
IsActive Bit
Default(1)
)

----
Insert values into table
INSERT
INTO
TBL_EMPLOYEE
(EmpId,
FirstName, LastName, Age, MobileNo
)
VALUE
(101,’Ryan’,’Arjun’,22,7856124580)

Keep in your mind that if you are not going to assign a name for unique constraint, check constraint and default constraint then system/SQL server will generate static constraint names for the unique, check and default constraint column.

Table Level Default Constraints -
You can create a default constraint after creating the table with the help of Alter
Table command as given below.


---- Table
Level - Default being added to another column
ALTER
TABLE
TBL_EMPLOYEE
ADD Sex Char(1)
Constraint  
DF_Emp_Sex Default(‘M’)

---- drop
Default Constraint
ALTER
TABLE
TBL_EMPLOYEE
Drop Constraint
 
DF_Emp_Sex

---- Table
Level - Default being added to existing column
ALTER
TABLE
TBL_EMPLOYEE
ADD Constraint
 
DF_Emp_Sex Default(‘M’) For Sex



What happen,if you have some data into your table?
If you have some data into your table, for example in Sex field you have data such as Null and you want to apply default constraint on this column. Then you can create Table level default constraint but system will not update the existing data and the existing data in this column will remain as Is.
In this case, SQL will allow this constraint to your column and will be provide the default
values for new incoming data if that value is not specified in the insert query.

To Watch a demo, please visit our YouTube channel - 

No comments:

Post a Comment

Popular Posts

Get Sponsored by Big Brands