sql
  1. sql-default-constraint
PreviousNext

SQL Constraints - Default Constraint

The DEFAULT Constraint is used to specify the default value for a column, when no value is provided during an INSERT operation. It allows you to provide a default value for a column, that will be inserted automatically by the database system if a new row is inserted and no value is specified for that column.

Syntax

CREATE TABLE table_name
(
column1 datatype DEFAULT default_value,
column2 datatype DEFAULT default_value,
column3 datatype DEFAULT default_value,
...
);

Example

Let us consider the following example, where we create a table called "Employees" with a few columns that have default values:

CREATE TABLE Employees
(
EmployeeID int PRIMARY KEY,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
Gender varchar(10) DEFAULT 'Male',
Salary decimal(10,2) DEFAULT 0.0
);

Output

If we insert a row with values for only "EmployeeID", "FirstName" and "LastName" columns, the default values for "Gender" and "Salary" columns will be inserted automatically. For example:

INSERT INTO Employees(EmployeeID, FirstName, LastName)
VALUES (1, 'John', 'Doe');

SELECT * FROM Employees;

The output will be:

+------------+-----------+----------+--------+--------+
| EmployeeID | FirstName | LastName | Gender | Salary |
+------------+-----------+----------+--------+--------+
| 1          | John      | Doe      | Male   | 0.00   |
+------------+-----------+----------+--------+--------+

Explanation

In the above example, we have created a table called "Employees" with columns such as "EmployeeID", "FirstName", "LastName", "Gender" and "Salary". We have provided default values for "Gender" and "Salary" columns, such as 'Male' and 0.0 respectively. During an INSERT operation, if a value is not provided for "Gender" or "Salary" columns, the default values will be inserted automatically.

Use

The DEFAULT Constraint is used to provide a default value for a column, which will be inserted automatically if a new row is inserted and no value is specified for that column. It is useful when you want to ensure that a column always has a value, even if a value is not explicitly provided during an INSERT operation.

Important Points

  • DEFAULT Constraint is used to provide a default value for a column, which is inserted automatically if a new row is inserted and no value is specified for that column.
  • It is useful when you want to ensure that a column always has a value, even if a value is not explicitly provided during an INSERT operation.
  • If you define a DEFAULT Constraint for a column, the database system will automatically insert the default value for that column during INSERT operations, unless a value is explicitly provided.
  • You can specify any value as the default value, including expressions and functions.

Summary

The DEFAULT Constraint is a useful tool in SQL, which allows you to specify a default value for a column in a table. This value will be inserted automatically when a new row is inserted into the table, if no value is explicitly provided for that column. You can use this constraint to ensure that your tables always contain valid data, even if some values are not explicitly provided during INSERT operations.

Unique ConstraintPrimary Key Constraint
Published on: