Default Constraint in sql server ~ MiTechDev.com

Mitechdev.com is a programming blog which focused on Web Programming and development

Tuesday, 7 March 2017

Default Constraint in sql server

07:00:00 Posted by raviteja swayampu , No comments
In previous tutorial of this series we learned about how to create and work(added primary and foreign key constraints to the tables) with tables in sql server..
How to create and work with tables in sql server
In this tutorial we are going to learn how to add DEFAULT constraint to the column in table.And in how many ways we can do that in sql server.before doing that we discuss about what is DEFAULT constraint.
DEFAULT:
A column default can be specified using Default constraint. The default constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified, including NULL.

We can add DEFAULT constraint to a table..
1.By altering column using ALTER command or
2.While adding a new column to existing table.

1.Using ALTER:
ALTER TABLE { TABLE_NAME }
ADD CONSTRAINT { CONSTRAINT_NAME }
DEFAULT { DEFAULT_VALUE } FOR { EXISTING_COLUMN_NAME 

2.While adding a new column:
ALTER TABLE { TABLE_NAME } 
ADD { COLUMN_NAME } { DATA_TYPE } { NULL | NOT NULL } 
CONSTRAINT { CONSTRAINT_NAME } DEFAULT { DEFAULT_VALUE }
Example:
ALTER TABLE tblPerson
ADD CONSTRAINT DF_tblPerson_GenderId
DEFAULT 1 FOR GenderId
The insert statement below does not provide a value for GenderId column, so the default of 1 will be inserted for this record.
Insert into tblPerson(ID,Name,Email) values(5,'Sam','[email protected]')
On the other hand, the following insert statement will insert NULL, instead of using the default.
Insert into tblPerson(ID,Name,Email,GenderId) values (6,'Dan','[email protected]',NULL)

To drop the constraint from the table's column we can use DROP command
Syntax to drop constraint:
ALTER TABLE { TABLE_NAME } 
DROP CONSTRAINT { CONSTRAINT_NAME }
In next tutorials we will learn more about Sql server...

0 comments:

Post a Comment