What is DEFAULT ?

The DEFAULT constraint in SQL is used to set a default value for a column in a table. This default value is automatically assigned to the column when a new record is inserted into the table, and no specific value is provided for that column. Here are the key points about the DEFAULT constraint:

Definition and Purpose

• The DEFAULT constraint provides a predefined value for a column if no value is specified during the insertion of a new record[1][2][3][4][5].
• It ensures that a column always has a value, which can be useful for maintaining data integrity and avoiding NULL values where they are not desired.

Usage in SQL Statements

Creating a Table with a DEFAULT Constraint

When creating a new table, you can specify a default value for a column using the DEFAULT keyword. For example:

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

In this example, if no value is provided for the City column, 'Sandnes' will be used as the default value[1][2][4][5].

Using System Functions as Default Values

You can also use system functions to set default values. For instance, using the current date:

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

Here, GETDATE() will insert the current date into the OrderDate column if no date is provided[1][2][5].

Adding a DEFAULT Constraint to an Existing Table

You can add a default constraint to an existing table using the ALTER TABLE statement:

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

This command sets 'Sandnes' as the default value for the City column in the Persons table[1][2][3][5].

Dropping a DEFAULT Constraint

To remove a default constraint from a column, you can use the following SQL command:

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;

This command removes the default value from the City column[1][2][4][5].

Examples

• Inserting Records with Default Values:
  sql Copy

  INSERT INTO Persons (ID, LastName, FirstName, Age) VALUES (1, 'Smith', 'John', 30);

  In this case, 'Sandnes' will be automatic...