SQL | DEFAULT Constraint
This constraint specifies a default value for the column when no value is specified by the user for all new records.
IDENTITY and timestamp columns aren’t associated with the default constraint.
Add Default Constraing in New Table
CREATE TABLE Employee (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Delhi',
DateOfBirth date DEFAULT GETDATE(),
Salary DECIMAL (18, 2) DEFAULT 10000.00,
DOB DateTime
)
We created the City, DateOfBirth, and Salary columns with the Default Constraint.
Now
If, while inserting the data into the Employee table, if the user does
not specify the values for City, DateOfBirth, and Salary columns, then
the default values we specified here are going to be stored in the store
City, DateOfBirth, and Salary columns.
Add Default Constraint in Existing Table
ALTER TABLE Employee
ADD DEFAULT (getutcdate()) FOR DOB;
insert into Employee (name,DepartmentName,Salary,Gender,Age,City) values ('Deepak','HR',40000,'Male',47,'Delhi')
-- DOB automatically filled with todays date
Drop/Remove Default Constraint
ALTER TABLE Employee
ALTER COLUMN DOB
DROP DEFAULT;
