SQL Reference - DEFAULT

Example

CREATE TABLE Employees (
            ID INT,
            Name VARCHAR(20),
            HasInsurance TINYINT DEFAULT 1
            );
INSERT INTO Employees (ID, Name) VALUES (1, 'John');
SELECT * FROM Employees;

Output

+------+-------+--------------+
| ID   | Name  | HasInsurance |
+------+-------+--------------+
| 1    | John  |     1        |
+------+-------+--------------+

Explanation

In this example, a new table named ‘Employees’ is created with three columns ‘ID’, ‘Name’, and ‘HasInsurance’. The ‘HasInsurance’ column has a default value of 1. Then, a new record is added where only the ‘ID’ and ‘Name’ values are specified. However, when all records are selected from the table, we see that the ‘HasInsurance’ field for John contains the default value of 1, since no particular value was specified during the row ‘John’ insertion.

Example

CREATE TABLE Customers (
  ID INT PRIMARY KEY,
  Name VARCHAR (100),
  SignDate DATE DEFAULT CURRENT_DATE
);

INSERT INTO Customers (ID, Name) VALUES (1, 'John Doe');

Output

SELECT * FROM Customers;

ID	Name	SignDate
1	John Doe	2022-01-12

#### Explanation
In this example, we created a table named 'Customers' with three columns: 'ID', 'Name', and 'SignDate'. The 'SignDate' column is assigned the default value of the current date (CURRENT_DATE), so if no value is provided during the INSERT operation, the current date will be automatically inserted. Consequently, when we inserted a new row providing values only for 'ID' and 'Name', the 'SignDate' column had the current date value inserted automatically.

</Fragment>

<Fragment slot="tab-3">SQL Server</Fragment>
<Fragment slot="panel-3">
#### Example

```sql
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int DEFAULT 30
);

INSERT INTO Persons (ID, LastName, FirstName) VALUES (1, 'Doe', 'John');

Output

SELECT * FROM Persons;

ID	LastName	FirstName	Age
1	Doe	John	30

Explanation

In the provided SQL code, a table named ‘Persons’ is created with columns ID, LastName, FirstName, and Age. The Age column is assigned a DEFAULT value of 30. So, when a new record is inserted into the ‘Persons’ table without providing a value for Age, SQL Server will automatically insert the DEFAULT value, which is 30 in this case. The output table shows the record of person named John Doe with an Age value of 30, despite no explicit Age value was provided during insertion.

Example

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

INSERT INTO Employees (ID, LastName, FirstName, Age)
VALUES (1, 'Doe', 'John', 30);

SELECT * FROM Employees;

Output

| ID | LastName | FirstName | Age |   City   |
|----|----------|-----------|-----|----------|
|  1 | Doe      | John      |  30 | Unknown  |

Explanation

The DEFAULT constraint is used to provide a default value for a column. In the given example, when a new record in the Employees table is being inserted without a value for the City field, the DEFAULT ‘Unknown’ will be implemented. Hence, under the City field for John Doe, the output shows ‘Unknown’.

Example

CREATE TABLE Employees (
    ID INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    Age INTEGER NOT NULL,
    Salary INTEGER DEFAULT 50000
);

INSERT INTO Employees (ID, Name, Age)
VALUES(1, 'John Doe', 25);

Output

sqlite> SELECT * FROM Employees;
1|John Doe|25|50000

Explanation

The SQL query created a table Employees with a DEFAULT value for the Salary column. When a new entry is inserted without specifying the Salary, as was done for ‘John Doe’, the DEFAULT value of 50000 is assigned.