SQL DEFAULT Constraint

What is a DEFAULT Constraint?

A DEFAULT constraint is used to provide a default value for a column when no value is specified during an insert operation. This helps maintain data integrity by ensuring that a column always has a valid value.

Syntax of DEFAULT Constraint

The basic syntax for defining a DEFAULT constraint is as follows:

CREATE TABLE table_name (
  column1 datatype DEFAULT default_value,
  column2 datatype,
  ...
);

Example of Using DEFAULT Constraint

Here’s an example of creating a table with a DEFAULT constraint:

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  Name VARCHAR(100),
  Age INT DEFAULT 18,
  Salary DECIMAL(10, 2) DEFAULT 30000.00
);

In this example, if no age or salary is specified when a new employee is added, the age will default to 18 and the salary to 30,000.00.

Multiple DEFAULT Constraints

You can define DEFAULT constraints on multiple columns in a table:

CREATE TABLE Products (
  ProductID INT PRIMARY KEY,
  ProductName VARCHAR(100),
  Price DECIMAL(10, 2) DEFAULT 0.00,
  Quantity INT DEFAULT 0
);

This ensures that if no price or quantity is specified, both will default to 0.

Important Considerations

When using the DEFAULT constraint, keep in mind:

• The default value must be compatible with the column's data type.
• DEFAULT constraints are only applied when a value is not specified during the insert operation.
• You can use expressions as default values (e.g., GETDATE() for current date/time).
• Only one DEFAULT constraint can be defined for each column.