Default Constraint in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

Default Constraint in MySQL with Examples

In this article, I am going to discuss Default Constraint in MySQL with Examples. Please read our previous article where we discussed NOT NULL Constraint in MySQL.

What is Default Constraint in MySQL?

As the name suggests the DEFAULT Constraint is used to set a default value for a data column. If the value for the column in the data row is not defined, the default value will be added to the data row column. In simple words, we can say that Default constraints enable MySQL to insert a default value to a column when the user doesn’t specify a value.

How to Add Default Constraint in MySQL?

We can add the DEFAULT Constraint in MySQL while creating a table using the CREATE TABLE statement, or using the ALTER TABLE statement for the existing table. We will understand both approaches.

Default constraint using CREATE table statement in MySQL:

Let us first understand how to add the DEFAULT Constraint using the CREATE table statement in MySQL. The following is the syntax to add Default Constraint using CREATE Table statement.

As you can see in the above query, you need to specify the table name table_name. Also, you need to mention the DEFAULT after the column definition of the field for which you want to add the default value. You also need to specify the default value of your column.

Examples to add Default Constraint using Create Table Statement:

Let us, first, create the Employee table by executing the following SQL Script.

CREATE TABLE Employee (
    ID INT NOT NULL,
    Name VARCHAR(255) NOT NULL,
    Age INT,
    Country VARCHAR(255) DEFAULT 'INDIA',
    DateOfJoining TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

As you can see in the above CREATE table statement, here, we created the Country and DateOfJoining columns with the Default Constraint. So, while inserting the data into the Employee table, if the user does not specify the values for the Country column, then the default value INDIA will be inserted. Similarly, if the user does not specify the value for the DateOfJoining column, then the default current timestamp value will be inserted.

Now, execute the following INSERT Statements.

Insert into Employee (ID, Name, Age) values(1, 'Anurag', 30);
Insert into Employee (ID, Name, Age, Country) values(2, 'Sambit', 35, 'UK');
Insert into Employee (ID, Name, Age, DateOfJoining) values(3, 'Priyanka', 30, '2017-04-22 10:05:33');
Insert into Employee (ID, Name, Age, Country, DateOfJoining) values(4, 'Hina', 27, 'USA', '2015-05-15 10:05:33');
Insert into Employee (ID, Name, Age, Country, DateOfJoining) values(5, 'Rohit', 28, 'India', '2016-06-14 10:05:33');

1. Statement1: In Statement 1, we have not specified the value for Country and DateOfJoining columns, so it will take the default values i.e. INDIA and Current timestamp for the Country and DateOfJoining columns.
2. Statement2: In Statement 2, we have not specified the value for the DateOfJoining column, so it will take the default Current timestamp value for the DateOfJoining column. In the Country column, it will store the given value i.e. UK.
3. Statement3: In Statement 3, we have not specified the value for the Country column, so it will take the default value i.e. INDIA for the Country column. In the DateOfJoining column, it will store the given value.
4. Statement4/5: In Statement 4 and 5, it will not insert the default values as we have specified the values for the Country and DateOfJoining columns.

Once you execute the INSERT Statements and if you verify the Employee table, then you will get the following records.

Default constraint using ALTER table statement in MySQL:

Let us understand how to add the DEFAULT Constraint using the ALTER table statement in MySQL. The following is the syntax to add Default Constraint using ALTER Table statement.

Examples to add Default Constraint using ALTER Table Statement:

In order to understand this, let us, first, create the Student table by executing the following SQL Script without any default constraints.

CREATE TABLE Student (
    Id INT NOT NULL,
    Name VARCHAR(255) NOT NULL,
    Age INT,
    Country VARCHAR(255), 
);

Now, we need to add DEFAULT Constraint for the Country column and need to set the default value to INDIA. Then we can use the ALTER Table statement to do the same as shown below.

ALTER TABLE Student ALTER Country SET DEFAULT ‘INDIA’;

Now, insert the following records.

INSERT INTO Student (Id, Name, Age, Country) VALUES (1, 'Anurag', 25, 'USA');
INSERT INTO Student (Id, Name, Age) VALUES (2, 'Hina', 24);
INSERT INTO Student (Id, Name, Age, Country) VALUES (3, 'Sambit', 25, 'India');

Now, verify the Student table and see the default value for the second statement.

How to DROP a DEFAULT Constraint in MySQL?

If you want to remove the default constraint, then you need to use the DROP DEFAULT statement. The following is the syntax to drop a default constraint in MySQL.

In the query, you need to specify the table_name and column_name for which you want to delete the DEFAULT constraint. For example, if you want to delete the Default Constraint for the Country column of the Student table, then you can do the same by using the following DROP Statement.

ALTER TABLE Student ALTER Country DROP DEFAULT;

Once you execute the above statement, now Insert data without specifying the Country value, and this time it will store NULL value.

When to use Default Constraint in MySQL?

In Real-time applications, sometimes we may need to add a default value to a column in MySQL and we don’t need that value to come from the user. For example, while creating an order, we may need to store the order date and we want the order date to be stored as the current date and we can easily do this by using the default constraint in MySQL.

Note: In a MySQL table, each column must contain a value including a NULL. While inserting data into a table, if no value is supplied to a column, then the column gets the value set as DEFAULT.

In the next article, I am going to discuss the UNIQUE Constraint in MySQL with Examples. Here, in this article, I try to explain DEFAULT Constraint in MySQL with Examples and I hope you enjoy this article.