In this tutorial, you will learn how to use the SQL Server DEFAULT constraint is used to set a default value for a column..
When we insert any record in the table, The default value will be added to all new records, if no other value is specified. The DEFAULT is a constraint in SQL which allows users to fill a column with the default or fixed value which you define with default.
The following CREATE TABLE statement defines a DEFAULT constraint on the FirstName column of the Employee table. you can use the CONSTRAINT keyword as follows:
CREATE TABLE Employee( Eid int NOT NULL, FirstName varchar(100) DEFAULT 'Rohatash', LastName varchar(100), Emailid varchar(100), Age int, Salary int, Departmentid int );
Now insert data in parent table - Employee. We have not insert any value in FirstName. By defualt name will show.
Insert into Employee(Eid,LastName,Emailid,Age,Salary,Departmentid) values(1, 'Kumar', 'Rohatash.email@example.com',101, 6000, 1) Select * from Employee
You can define a DEFAULT constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Employee ( Eid int NOT NULL, FirstName varchar(100) DEFAULT 'Rohatash', LastName varchar(100), Emailid varchar(100), Age int, Salary int DEFAULT '2000', Departmentid int );
To add the DEFAULT constraint to an existing column, The Employee table is already created, use the following SQL Query:
ALTER TABLE Employee ADD CONSTRAINT df_salary DEFAULT '200' FOR Salary;
To remove the DEFAULT constraint from a column, you can use the ALTER TABLE ALTER COLUMN statement as follows:
ALTER TABLE Employee DROP CONSTRAINT df_salary;