Oracle DEFAULT Constraint

Summary: in this tutorial, you’ll learn how to use the Oracle DEFAULT constraint to set a default value for a column.

Introduction to Oracle DEFAULT constraint #

In Oracle, the DEFAULT constraint allows you to sets a default for a column when you don’t provide a value during an INSERT.

When you define a column of a table without using a constraint such as NOT NULL, Oracle will insert NULL if you provide no value when inserting data into the table.

Note that the DEFAULT constraint does not apply to UPDATE statements.

The DEFAULT constraint can help supply a default value and reduce NULLs in your data.

Here’s the syntax for defining a default value for a column when creating a new table:

CREATE TABLE table_name(
   id INT PRIMARY KEY,
   colunm_name datatype DEFAULT default_value
);Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify a default value after the DEFAULT keyword. When you insert a row into the table without specifying a value for the column_name, Oracle will use the defafult_value.

Note that you can combine the DEFAULT constraint with other constraints such as NOT NULL, CHECK and UNIQUE.

To add a default constraint to an existing column, you can use the ALTER TABLE statement:

ALTER TABLE table_name
MODIFY column_name DEFAULT default_value;Code language: SQL (Structured Query Language) (sql)

Oracle DEFAULT constraint examples #

Let’s take some examples of using the DEFAULT constraints.

Setting a fixed default value for a column #

First, create a new table called payments:

CREATE TABLE payments(
   id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   customer_id NUMBER NOT NULL,
   amount NUMBER(11,2) NOT NULL,
   status VARCHAR2(25) DEFAULT 'Pending' NOT NULL,
   payment_date DATE
);Code language: SQL (Structured Query Language) (sql)

In the payments table, the status column has a default value ‘Pending’. Note that the NOT NULL cconstraint must appear after the DEFAULT constraint, or you’ll encounter an error.

Second, insert a new row into the payments table:

INSERT INTO payments(customer_id, amount, payment_date)
VALUES(1, 21000, DATE '2025-05-12');Code language: SQL (Structured Query Language) (sql)

Third, query data from the payments table:

SELECT * FROM payments;Code language: SQL (Structured Query Language) (sql)

Output:

The output indicates that Oracle uses the default value 'Pending' to insert into the status column.

Using a DATE function as a default value #

First, add a new column created_at to the payments table:

ALTER TABLE payments
ADD COLUMN created_at TIMESTAMP WITH TIME ZONE 
DEFAULT CURRENT_TIMESTAMP;Code language: SQL (Structured Query Language) (sql)

Second, retrieve data from the payments table:

SELECT * FROM payments;Code language: SQL (Structured Query Language) (sql)

You’ll see a new column created_at is updated with the time when you run the ALTER TABLE column.

Third, insert a new row into the payments table:

INSERT INTO payments(customer_id, amount, payment_date)
VALUES(2, 32000, DATE '2025-05-12');Code language: SQL (Structured Query Language) (sql)

Since we don’t provide a value for the created_at column, Oraccle will use the timestamp when we execute the INSERT statement.

Finally, retrieve data from payments table:

SELECT * FROM payments;Code language: SQL (Structured Query Language) (sql)

Output:

Notice that you’ll see different values in the created_at column.

Summary #

• Use a DEFAULT constraint to set a default value for column when no value is provided during an insert.