Default Constraint in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Default Constraint in Oracle with Examples

In this article, I am going to discuss Default Constraint in Oracle with Examples. Please read our previous article where we discussed How to Disable and Enable Constraints in Oracle with Examples.

What is Default Constraint in Oracle?

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

How to Add Default Constraint in Oracle?

We can add the DEFAULT Constraint in Oracle 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 Oracle:

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

<COLUMN NAME> <DATATYPE>[SIZE] DEFAULT <VALUE / EXPRESSION>

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 VARCHAR2(20) NOT NULL,
    Age INT,
    Country VARCHAR2(10) DEFAULT 'INDIA',
    DOJ DATE DEFAULT SYSDATE
);

As you can see in the above CREATE table statement, here, we created the Country and DOJ 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 DOJ column, then the default current date 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, DOJ) values(3, 'Priyanka', 30, '25-DEC-17');
INSERT INTO Employee (ID, Name, Age, Country, DOJ) values(4, 'Hina', 27, 'USA', '20-OCT-18');
INSERT INTO Employee (ID, Name, Age, Country, DOJ) values(5, 'Rohit', 28, 'India', '25-JAN-16');

1. Statement1: In Statement 1, we have not specified the value for Country and DOJ columns, so it will take the default values i.e. INDIA and Current system date for the Country and DOJ columns.
2. Statement2: In Statement 2, we have not specified the value for the DOJ column, so it will take the default system date value for the DOJ 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 DOJ 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 DOJ 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 Oracle:

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

ALTER TABLE <TN> MODIFY <COLUMN NAME> DEFAULT <VALUE / EXPRESSION>;

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 VARCHAR2(20) NOT NULL,
    Age INT,
    Country VARCHAR2(20)
);

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

ALTER TABLE Student MODIFY Country 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 as shown in the below image.

Note: DEFAULT Constraint is a special type of constraint which is used to assign a user-defined default value to a column

How to view the default value of a column in Oracle?

If you want to view the default value of a column then you need to use the “USER_TAB_COLUMNS” data dictionary. The following query will give the list of columns and the default value associated with that column.

SELECT COLUMN_NAME, DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME='EMPLOYEE';

When you run the above query, you will get the following output.

How to remove the default value of a column?

ALTER TABLE Student MODIFY Country DEFAULT NULL;

When to use Default Constraint in Oracle?

In Real-time applications, sometimes we may need to add a default value to a column in Oracle 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 Oracle.

In the next article, I am going to discuss Joins in Oracle with Examples. Here, in this article, I try to explain Default Constraint in Oracle with Examples and I hope you enjoy this Default Constraint in Oracle with Examples article