Hey folks till now we have discussed User defined functions in sql, Kill Processes in sql server 2008, View in sql ,Remove Cross duplicate rows in SQL, Recursive SQL Query , Recursive SQL Query-2, STUFF and CONCAT in SQL, RANK in SQL server , Difference between temporary table and table variable in sql server, UNIQUEIDENTIFIER in SQL Server, RAW Mode with FOR XML , AUTO Mode with FOR XML, EXPLICIT Mode with FOR XML , PATH Mode with FOR XML, OUTPUT Clause in SQL Server, Difference between delete and truncate in sql server etc.

Today we will discuss regarding default constraint in SQL. We have a ability to specify DEFAULT value in the column in sql. So if we don’t pass any value for the column it will automatically assign the default specified value to the column for that record. This will be more useful when you don’t want to insert value to the column but still you want some value to be inserted in the column.

Lets take a example of this. Suppose i have a column which contains the date when the record last updated. Here in this case i don’t want to insert a current date each time the record get updated. So i simply set the default value of the column to the GETDATE() and this will assign current date and time to the column value.

CREATE TABLE Employee
(
EmployeeId INT IDENTITY(1,1),
FirstName VARCHAR(20),
MiddleName VARCHAR(20),
LastName VARCHAR(20),
Address1 VARCHAR(50),
Address2 VARCHAR(50),
City VARCHAR(20),
State VARCHAR(20),
dte_Updated DATETIME DEFAULT GETDATE()
)

The above code will create a table with dte_Updated column as default value of GETDATE(). So each time we insert some value to the table it will automatically assign current date and time to the column.

Now with the help of the below code we can alter the table to assign the default constraint to the existing column.

ALTER TABLE Employee
ADD CONSTRAINT DefaultState
DEFAULT ‘Texas’ FOR State

 

To remove the default constraint on the column we can simply drop the constraint with the help of the below code.

ALTER TABLE Employee
DROP CONSTRAINT DefaultState

Hope this would help.