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.)
      

**Note:** The syntax for adding and dropping default constraints can vary slightly across different database systems. Always consult the documentation for your specific database system. The example uses `GETDATE()` which is a SQL Server function. For other databases you'll use the appropriate function for getting the current date and time (e.g., `NOW()` in MySQL, `CURRENT_TIMESTAMP` in some other systems).