SQL DEFAULTConstraints

The DEFAULT constraint is used to insert a default value into a column.

If no other value is specified, the default value will be added to all new records.

Syntax

1. Define a DEFAULT constraint when creating a table:

CREATE TABLE 表名 (
    列名 数据类型 DEFAULT 默认值
);

2. Add a DEFAULT constraint to an existing table:

ALTER TABLE 表名
ALTER COLUMN 列名 SET DEFAULT 默认值;

Example

1. Define a DEFAULT constraint when creating a table

Example

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE DEFAULT GETDATE(), -- Default value is the current date
    Salary DECIMAL(10, 2) DEFAULT 0.00 -- Default value is 0.00
);

2. Add a DEFAULT constraint to an existing table

ALTER TABLE Employees
ALTER COLUMN Salary SET DEFAULT 0.00;

3. Use default values when inserting data

INSERT INTO Employees (EmployeeID, FirstName, LastName)
VALUES (1, 'John', 'Doe');

If no values are provided for HireDate and Salary, the database will automatically use the default values.

Drop DEFAULT Constraint

The way to drop it differs between different databases:

1、SQL Server

ALTER TABLE 表名
DROP CONSTRAINT 约束名;

2、MySQL

ALTER TABLE 表名
ALTER COLUMN 列名 DROP DEFAULT;

3、Oracle

ALTER TABLE 表名
MODIFY 列名 DEFAULT NULL;

4、MS Access

ALTER TABLE 表名
ALTER COLUMN 列名 DROP DEFAULT;

Notes

  • DEFAULTThe value of the constraint must be compatible with the data type of the column.

  • If the column is defined asNOT NULLand no default value is provided, you must explicitly provide a value when inserting data, otherwise an error will be reported.

  • The default value can be a constant, an expression, or a function (such asGETDATE()）。

Applicable Scenarios

  • Set the current date as the default value for a date column.

  • Set an initial value for a numeric column (e.g.,0）。

  • Set a default status for a status column (e.g.,'Active'）。

Other Extensions