How to use Default Constraint on Column in SQL

March 13, 2014 , , , 0 Comments

A default constraint can be used to assign a constant value to a column, and the user need not insert value for such a column. Only one default constraint can be created for a column but the column cannot be an identity column. The system-supplied values, such as USER, CURRENT_USER, and user-defined values can be assigned as defaults.

The syntax of applying the default constraint while creating a table is:

CREATE TABLE table_name
Col_name [CONSTRAINT constraint_name] DEFAULT (constant_expression|NULL)
(col_name [, col_name [, …]])

  • Constraint_name specifies the name of the constraint to be created.
  • Constant_expression specifies an expression that contains only constant values. This can contain a NULL value.

In the example of creating the EmployeeLeave table, you can insert a default constraint to add a default value for the LeaveType column. You can set the default leave type as PL. You can use the following statement to create the default constraint:

CREATE TABLE HumanResources.EmployeeLeave
EmployeeID int CONSTRAINT fkEmployeeID FOREIGN KEY REFERENCES Humanresources.Employe (EmployeeID),
LeaveStartDate datetime CONSTRAINT cpkLeaveStartDate PRIMARY LeaveEndDate datetime NOT NULL,
LeaveReason varchar(100),
LeaveType char(2) CONSTRAINT chkLeave CHECK (LeaveType IN (‘ CL’, ‘SL’, ‘PL’)) CONSTRAINT chkDefLeave DEFAULT ‘PL’)

The preceding command creates the EmployeeLeave table with a default constraint on the LeaveType column, where the default value is specified as PL. The name of the constraint is chkDefLeave.

Check on Constraints

Rhett Butler

Some say he’s half man half fish, others say he’s more of a seventy/thirty split. Either way he’s a fishy bastard. Google