What is Default Constraint in SQL?

Structured query language (SQL) is a very powerful database language for managing and querying huge data sets in RDBMS. SQL can define constraints on data fields, which ensures that valid data is entered into a table of a database. Default constraints are one of the most common types of constraint used in SQL. In this article post, you will learn the concept of Default Constraint in SQL. We will discuss its definition, types, syntax, examples, benefits, and limitations. So, let’s get started!

Definition of Default Constraint in SQL

A default constraint in SQL is a type of constraint that is used to assign a default value to a column in the database table. Whenever a new row or record is inserted into the table and no value is specified for the column, then the assigned default value will be automatically inserted into the table. Default constraints are used to ensure that all records in a table have a value for a particular column, even if the value is not provided while insertion.

How Does Default Constraint Work?

A Default Constraint can be added to a table during table creation or added later using the ALTER TABLE statement. When a Default Constraint is added to a column, the default value is specified using a default expression. The default expression can be a constant value, a function, or an expression that evaluates to a value.

When a new row is inserted into a table, and no value is provided for a column with a Default Constraint, the default value is inserted into the column. For example, consider a table called “Employees” with a column called “Age.” If a Default Constraint is added to the “Age” column with a default value of 30, any new employee inserted into the “Employees” table without an “Age” value will automatically be assigned an “Age” value of 30.

What are the types of Default Constraints in SQL?

There are two types of default constraints in SQL –

1. Column Level Default Constraints- Column level default constraints are defined on a specific column in a table while creating a table. They apply only to that column and are used to assign a default value to that column.
2. Table Level Default Constraints – Table level default constraints are defined separately and then add it to a specific column while creating a table or after creating a table. They apply to all columns or specific columns in the table and are used to assign default values to multiple columns at once.

How to Create Default Constraints?

Creating a default constraint in SQL is a very simple process. Refer below steps involved to create a default constraint –

1. Determine the column or columns for which you want to create a default constraint.
2. Decide the default value that you want to assign to the column or columns.
3. Use the ALTER TABLE statement to add the default constraint to the table.

Refer to the below syntax example to create a default constraint while Creating a new table –

Syntax:

CREATE TABLE Table_Name (
Column1 Data_Type Constraint_Name DEFAULT 'Default_Value',
Column2 Data_Type Constraint_Name DEFAULT 'Default_Value',
Column3 Data_Type Constraint_Name DEFAULT 'Default_Value',
….
);

In the above example, Table_Name is the name of the table you want to create, Column1, Column2, Column3, etc., are the names of the columns, Data_Type is the data type of the column, Constraint_Name is the name of the default constraint, and Default_Value is the default value you want to set for the column.

Refer to the below syntax example to create a default constraint after a table creation –

Syntax:

ALTER TABLE Table_Name
ADD DEFAULT 'Default_Value' FOR Column_Name'

ALTER TABLE Table_Name
ADD CONSTRAINT Default_Name DEFAULT 'Default_Value' FOR Column_Name;

In the above example, we are adding a default constraint to the Column_Name column in the Table_Name table. The default value for the column is ‘Default_Value’.

What are the Benefits of Default Constraints in SQL?

Refer to below benefits of default constraints –

1. Ensuring Data Consistency: Default Constraint ensures that a specific column always contains a particular value if no other value is specified while inserting data into a table and ensures data consistency.
2. Reducing Data Entry Time: Default Constraint allows you to set a default value for a column. This means you do not have to enter a value every time you add a new record. This reduces the time it takes to enter data and increases the efficiency of the database.
3. Simplifying Database Design: With Default Constraint, you can simplify the database design by reducing the number of NULL values. Instead of leaving a column empty, you can set a default value, which makes the table more organized and easier to read.
4. Enhancing Data Integrity: Default Constraint enhances data integrity by ensuring that a column always has a valid value. It reduces the risk of data corruption and ensures that the database maintains a high level of accuracy.

What are the Limitations of Default Constraints in SQL?

Refer to below limitations of default constraint in SQL –

1. Limited Functionality: Default Constraint is limited to set default values for columns. It cannot enforce complex business rules or constraints that require more advanced programming techniques.
2. Limited Portability: Default Constraint syntax and functionality may vary across different database management systems. This can cause problems when migrating databases from one system to another.
3. Limited Flexibility: Once a default constraint is set, it cannot be easily changed or removed without affecting the existing data in the table.

How Default Constraints can be used in SQL?

Let’s take some examples to look at how default constraints can be used in SQL, refer to below –

Example 1: Setting a Default Date, Suppose you have a table called orders that include a column called order_date. You want to ensure that every row in the table has an order date, even if the user doesn’t specify one. You can use a default constraint to assign the current date to the order_date column by default as given below –

ALTER TABLE orders
ADD DEFAULT GETDATE() FOR order_date;

In the above example, if a user inserts a new row into the orders table and does not specify an order date, Then the current date will be assigned automatically by the database itself.

Example 2: Setting a Default Boolean Value, Suppose you have a table called employees that includes a column called is_manager. You want to ensure that every new employee is assigned a value for the is_manager column, even if the user does not specify one. You can use a default constraint to assign a default value of FALSE to the is_manager column as given below –

ALTER TABLE employees
ADD DEFAULT 0 FOR is_manager;

In the above example, every time a new employee is added to the table, the is_manager column will automatically be assigned a value of FALSE.

Example 3: Setting a Default Value for a Null Column, Suppose you have a table called products that includes a column called price. You want to ensure that every product in the table has a price, even if the user does not specify one. You can use a default constraint to assign a default value of 0.00 to the price column as given below –

ALTER TABLE products
ADD DEFAULT 0.00 FOR price;

In the above example, if a user adds a new product to the table and does not specify a price, the default value of 0.00 will be assigned automatically.

Applications of Default Constraint in SQL

You will learn here the application of default constraint in SQL as follows –

1. Default Constraint Creation While Table Creation

You will learn here to create a default constraint while creating a new table.

A. Default Constraint Creation on a Column Without Defining Its Name While Table Creation

In this example, we will create a table named ‘DF_Employees1’ and assign a default constraint without defining its name with the ‘DateOfBirth’ column and default value ‘1970-01-01’. In this scenario, DBMS will create a default constraint and also define a system-generated default name for this default constraint which we will see ahead. Refer to the below syntax –

Syntax:

CREATE TABLE Table_Name (
Column1 Data_Type,
Column2 Data_Type,
Column3 Data_Type,
Column4 Data_Type DEFAULT 'Default_Value');

Example:

CREATE TABLE DF_Employees1 (
EmployeeID INT NOT NULL,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
DateOfBirth DATE DEFAULT '1970-01-01');

Screenshot: Below screenshot for reference –

You can see in the above query example we have not provided any specific name for this default constraint. In this scenario, DBMS has provided a system-generated default name for this default constraint (DF__DF_Employ__DateO__65370702), which can be seen in the above screenshot marked with yellow color on the left side.

B. Default Constraint Creation on a Column With Defining Its Name While Table Creation

In this example, we will create a table named ‘DF_Employees2’ and assign a default constraint named ‘DF_Employees_DateOfBirth’ with the ‘DateOfBirth’ column. Refer to the below syntax –

Syntax:

CREATE TABLE Table_Name (
Column1 Data_Type,
Column2 Data_Type,
Column3 Data_Type,
Column4 Data_Type CONSTRAINT Constraint_Name DEFAULT 'Default_Value'
);

Example:

CREATE TABLE DF_Employees2 (
EmployeeID INT NOT NULL,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
DateOfBirth DATE CONSTRAINT DF_Employees_DateOfBirth DEFAULT '1970-01-01'
);

Screenshot: Below screenshot for reference –

You can see in the above query example we have provided the default constraints name ‘DF_Employees_DateOfBirth’, which can be seen in the above screenshot marked with yellow color on the left side.

2. Default Constraint Creation After Table Creation

You will learn here to create a default constraint after creating a new table.

A. Default Constraint Creation on a Column Without Defining Its Name after Table Creation

In this example, We will create a default constraint without providing its name with the ‘DateOfBirth’ column of an existing table named ‘DF_Employees3’. In this scenario, DBMS will provide a system-generated default name for this default constraint which we will see ahead. Refer to the below syntax –

Syntax:

ALTER TABLE Table_Name
ADD DEFAULT 'Default_Value' FOR Column_Name;

Example:

ALTER TABLE DF_Employees3
ADD DEFAULT '1970-01-01' FOR DateOfBirth;

Screenshot: Below screenshot for reference –

You can see in the above query example we have not provided any specific name for this default constraint. In this scenario, DBMS has provided a system-generated default name for this default constraint (DF__DF_Employ__DateO__690797E6), which can be seen in the above screenshot marked with yellow color on the left side.

B. Default Constraint Creation on a Column With Defining Its Name after Table Creation

In this example, We will create a default constraint by providing its name ‘DF_Employees4_DateOfBirth’ with the ‘DateOfBirth’ column of an existing table named ‘DF_Employees4’. Refer to the below syntax –

Syntax:

ALTER TABLE Table_Name
ADD CONSTRAINT Constraint_Name DEFAULT 'Default_Value' FOR Column_Name;

Example:

ALTER TABLE DF_Employees4
ADD CONSTRAINT DF_Employees4_DateOfBirth DEFAULT '1970-01-01' FOR DateOfBirth;

Screenshot: Below screenshot for reference –

You can see in the above query example we have provided the default constraints name ‘DF_Employees4_DateOfBirth’ for this default constraint, which can be seen in the above screenshot marked with yellow color on the left side.

3. Delete or Drop a Default Constraint

You will learn here to delete or drop an existing Default constraint. Refer to the below syntax –

Syntax:

ALTER TABLE Table_Name
DROP CONSTRAINT Constraint_Name

Example:

ALTER TABLE DF_Employees4
DROP CONSTRAINT DF_Employees4_DateOfBirth

Screenshot: Below screenshot for reference –

You can see in the above query example we have deleted the default constraint ‘DF_Employees4_DateOfBirth’, which can not be seen in the above screenshot marked with yellow color on the left side.

4. To Check List of all Default Constraints in SQL Server

You will learn here to do checklists of present default constraints in your SQL Server database. Refer to the below syntaxes –

Example 1:

SELECT con.[name] AS Constraint_Name,
schema_name(t.schema_id) + '.' + t.[name] AS Table_Name,
col.[name] AS Column_Name,
con.[definition]
FROM sys.default_constraints con
left outer join sys.objects t
ON con.parent_object_id = t.object_id
LEFT OUTER JOIN sys.all_columns col
ON con.parent_column_id = col.column_id
AND con.parent_object_id = col.object_id
ORDER BY con.name;

Screenshot: Below screenshot for reference –

You can see in the above query example all the default constraints lists are showing. You can use the below syntax example too for a better understanding –

Example 2:

SELECT
DB_Name() AS Database_Name,
Schema_name(t.Schema_id)AS Schema_Name,
t.name AS Table_Name,
c.name AS Column_Name,
d.name AS DefaultConstraint_Name,
d.definition AS DefaultConstraint_Definition
FROM sys.default_constraints d
INNER JOIN sys.columns c ON
d.parent_object_id = c.object_id
AND d.parent_column_id = c.column_id
INNER JOIN sys.tables t ON
t.object_id = c.object_id;

Screenshot: Below screenshot for reference –

You can see in the above query example all the default constraint lists are showing with the database names and schemas.

Test Scenario of Default Constraint in SQL Server

You will learn here all test scenarios of default constraint that is it working or not using INSERT – SELECT – INSERT statements.

Using INSERT Statement

If you remembered that we have created ‘DF_Employees2’ and implemented a default constraint named ‘DF_Employees_DateOfBirth’, now we will take this table for testing with insert statement whether default constraint ‘DF_Employees_DateOfBirth’ is working properly or not as follows –

Example:

INSERT INTO DF_Employees2 (EmployeeID, FirstName, LastName, DateOfBirth ) VALUES
('12','FirstName1','LastName2','2020-03-23'),
('13','FirstName2','LastName3','2019-04-22'),
('14','FirstName3','LastName4','2018-05-21'),
('15','FirstName4','LastName5','2017-06-20')

Screenshot: Below screenshot for reference –

Using SELECT Statement

You can see in the above query that we have provided values for all the columns including the ‘DateOfBirth’ column which is set to default ‘1970-01-01’ and found that all records have been inserted into the ‘DF_Employees2’ table. You can see the all inserted records using the SELECT statement as shown below screenshot –

Screenshot: Below screenshot for reference –

Using INSERT Statement

As we can see in the above screenshot, all the values are inserted into the table successfully. There is no default value inserted into the ‘DateOfBirth’ column. Now we will try to insert another data into the same table without providing data for the ‘DateOfBirth’ column, In this case, the default value ‘1970-01-01’ should be inserted automatically by the DBMS as follows –

Example:

INSERT INTO DF_Employees2 (EmployeeID, FirstName, LastName ) VALUES
('16','FirstName5','LastName6'),
('17','FirstName6','LastName7'),
('18','FirstName7','LastName8'),
('19','FirstName8','LastName9')

Screenshot: Below screenshot for reference –

You can see in the above query that we have provided values for all the columns excluding the ‘DateOfBirth’ column which is set to default value ‘1970-01-01’ and found that all records have been inserted into the ‘DF_Employees2’ table successfully.

Using SELECT Statement

You can see the all inserted records using the SELECT statement as shown below screenshot –

Screenshot: Below screenshot for reference –

You can see in the above screenshot, all the provided values with the default value for the ‘DateOfBirth’ column are inserted successfully. DBMS has provided a default value for the ‘DateOfBirth’ column itself, which means our default constraint is working properly.

Note: We have tried to explain all possible scenarios about the default constraint at a summary level, where you have to read the complete article and try to elaborate on all possible scenarios with you and give more time to practice it.

FAQs

Conclusion

Default Constraint in SQL is a useful feature that allows you to set a default value for a column. It ensures data consistency, reduces data entry time, simplifies database design, and enhances data integrity. However, it has some limitations, such as limited functionality, limited portability, and limited flexibility. It’s important to understand the syntax and types of default constraints in SQL and use them appropriately in your database design to optimize its performance and efficiency. We hope you would have liked this very well.

Please share this on your social media network so that it can be available to even more needy persons which may be beneficial for them. If you have any questions, feedback, or suggestions regarding this tutorial, please contact us. Do comment right in the comments section below. We will consider your valuable input and try to give you a response asap.

Recommended Article Posts

DDL Full Form and How is it used in SQL?	DML Full Form and How is it used in SQL?
DCL Full Form and How is it used in SQL?	TCL Full Form and How is it used in SQL?
What is DQL in SQL?	What are Integrity Constraints in DBMS?
Key Constraints in DBMS With Fundamentals	Primary Key Constraint in SQL
Unique Key in DBMS and Best Practices	Foreign Key in DBMS and Best Practices
What is Default Constraint in SQL?	What is Check Constraint in SQL?