SQL DEFAULT Constraint

The DEFAULT constraint in SQL is used to set a default value for a column in a table. This default value is automatically inserted into the column when a new row is added, if no specific value is provided for that column during the insertion.

Adding a DEFAULT Constraint During Table Creation

The most common way to add a default constraint is when you create a table using the CREATE TABLE statement.

Example: Adding a DEFAULT Constraint during CREATE TABLE (MySQL, SQL Server, Oracle, MS Access)

This example shows how to create a "Persons" table with a default value of 'Sandnes' for the "City" column.

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
);
            

-- The output will be a confirmation that the table was created successfully.
            

Example: Using Functions for DEFAULT Values (e.g., GETDATE())

You can also use functions like GETDATE() (in SQL Server) or equivalent functions in other databases to automatically insert system values (like the current date and time) as defaults.

CREATE TABLE Orders (
    ID int NOT NULL,
    OrderNumber int NOT NULL,
    OrderDate date DEFAULT GETDATE()
);
            

-- The output will be a confirmation that the table was created successfully.
            

Adding a DEFAULT Constraint to an Existing Table

If your table already exists, you can add a default constraint using the ALTER TABLE statement. The syntax varies slightly depending on the database system.

Example: Adding a DEFAULT Constraint using ALTER TABLE

The following examples show how to add a default value of 'Sandnes' to the "City" column of an existing "Persons" table in different database systems.

ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';
        

ALTER TABLE Persons
ADD CONSTRAINT df_City
DEFAULT 'Sandnes' FOR City;
        

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'Sandnes';
        

ALTER TABLE Persons
MODIFY City DEFAULT 'Sandnes';
        

-- The output will be a confirmation that the alteration was successful.
            

Removing a DEFAULT Constraint

To remove a default constraint, use the DROP DEFAULT command. Again, the syntax varies slightly among database systems.

Example: Removing a DEFAULT Constraint

These examples show how to remove the default constraint from the "City" column in the "Persons" table.

ALTER TABLE Persons
ALTER City DROP DEFAULT;
        

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
        

-- The output will be a confirmation that the alteration was successful.