SQL DEFAULT Constraint
The DEFAULT
constraint in SQL specifies a default value for a column. If you don't provide a value when inserting a new row, the database automatically uses the default value.
DEFAULT Constraint: Definition and Usage
DEFAULT
constraints simplify data entry and ensure that columns always have a value, even if a value isn't explicitly provided during insertion. They're very useful for setting default values for things like dates, status flags, or other attributes that might have a standard value if no other value is given. The default value can be a literal value or the result of a function (like GETDATE()
for the current date and time).
Adding DEFAULT Constraints
You can add DEFAULT
constraints when creating a table (using CREATE TABLE
) or to an existing table (using ALTER TABLE
). The syntax varies slightly depending on your specific database system (MySQL, SQL Server, PostgreSQL, etc.).
DEFAULT Constraint on CREATE TABLE
This example creates a 'Persons' table with a default city of 'Sandnes'.
Syntax
CREATE TABLE Persons (
City VARCHAR(255) DEFAULT 'Sandnes'
);
Output
The 'Persons' table is created. The 'City' column will automatically have the value 'Sandnes' for any new rows unless a different value is specified during insertion.
Using Functions for Default Values
You can use functions to provide dynamic default values (this example uses GETDATE()
to automatically set the 'OrderDate' to the current date and time):
Syntax
CREATE TABLE Orders (
OrderDate DATE DEFAULT GETDATE()
);
Output
Creates the 'Orders' table. New rows inserted into this table will automatically have the 'OrderDate' column populated with the current date and time.
Adding a DEFAULT Constraint After Table Creation
This shows how to add a default value to an existing column. Note the slight differences in syntax between the database systems.
Syntax (MySQL)
ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';
Syntax (SQL Server)
ALTER TABLE Persons
ADD CONSTRAINT df_City
DEFAULT 'Sandnes' FOR City;
Syntax (MS Access)
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'Sandnes';
Syntax (Oracle)
ALTER TABLE Persons
MODIFY City DEFAULT 'Sandnes';
Output
Adds a default value of 'Sandnes' to the 'City' column. New rows will have 'Sandnes' in the City column unless another value is specified during insertion.
Dropping DEFAULT Constraints
To remove a default constraint, use DROP DEFAULT
(or a similar command, depending on the database system).
Syntax (MySQL)
ALTER TABLE Persons
ALTER City DROP DEFAULT;
Syntax (SQL Server, Oracle, MS Access)
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
Output
(The default value for the 'City' column is removed.)