SQL DEFAULT

DEFAULT in SQL

The DEFAULT constraint in SQL is used to set a default value for a column. When a new record is inserted into the table and no value is specified for a column with a DEFAULT constraint, the default value is automatically applied. This ensures that no column is left empty unless explicitly specified.

Characteristics of the DEFAULT Constraint

• The DEFAULT constraint is applied to columns during table creation or modification.
• It applies to new rows only; existing rows remain unaffected when the default value is added.
• Supports both static values (e.g., a string or number) and system-generated values (e.g., functions like GETDATE() or CURRENT_TIMESTAMP).

How to Apply DEFAULT Constraint

The DEFAULT constraint can be applied when creating a table or added to an existing table.

1. Adding DEFAULT During Table Creation

Static Default Value

1
2
3
4
5
6
CREATE TABLE Employees (
    EmployeeID INT NOT NULL,
    Name VARCHAR(100) NOT NULL,
    Department VARCHAR(50) DEFAULT 'General',
    HireDate DATE DEFAULT '2024-01-01'
);

• The Department column defaults to "General" if no value is provided.
• The HireDate column defaults to 2024-01-01.

System-Generated Default Value

1
2
3
4
5
CREATE TABLE Orders (
    OrderID INT NOT NULL,
    OrderDate DATE DEFAULT GETDATE(),
    Status VARCHAR(20) DEFAULT 'Pending'
);

• The OrderDate column defaults to the current date using the GETDATE() function.
• The Status column defaults to "Pending".

2. Adding DEFAULT to an Existing Table

If the table already exists, you can use the ALTER TABLE statement to add a default constraint.

MySQL

1
2
ALTER TABLE Employees  
ALTER COLUMN Department SET DEFAULT 'General';

SQL Server

1
2
ALTER TABLE Employees  
ADD CONSTRAINT df_Department DEFAULT 'General' FOR Department;

3. Removing a DEFAULT Constraint

You can drop a DEFAULT constraint if it's no longer needed.

MySQL

1
2
ALTER TABLE Employees  
ALTER COLUMN Department DROP DEFAULT;

SQL Server / Oracle

1
2
ALTER TABLE Employees  
DROP CONSTRAINT df_Department;

Common Use Cases

1. Default Status: Set default values for order statuses (e.g., "Pending").
2. Default Location: Set default locations like country or city when unspecified.
3. System-Generated Timestamps: Automatically add current dates or times to records when they are created.

Key Points to Remember

• The DEFAULT constraint sets a predefined value for a column when no value is provided.
• It supports both static values and system-generated values using functions.
• You can add or remove default constraints using ALTER TABLE.
• Default constraints apply to new records only; they do not affect existing data in the table.