{"id":5311,"date":"2025-05-11T21:33:17","date_gmt":"2025-05-12T04:33:17","guid":{"rendered":"https:\/\/www.oracletutorial.com\/?page_id=5311"},"modified":"2025-05-11T21:49:22","modified_gmt":"2025-05-12T04:49:22","slug":"oracle-default","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-default\/","title":{"rendered":"Oracle DEFAULT Constraint"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the Oracle DEFAULT<\/code> constraint to set a default value for a column.<\/p>\n\n\n\n

Introduction to Oracle DEFAULT constraint #<\/a><\/h2>\n\n\n\n

In Oracle, the DEFAULT<\/code> constraint allows you to sets a default for a column when you don’t provide a value during an INSERT<\/code>.<\/p>\n\n\n\n

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

Note that the DEFAULT<\/code> constraint does not apply to UPDATE<\/code> statements.<\/p>\n\n\n\n

The DEFAULT<\/code> constraint can help supply a default value and reduce NULL<\/code>s in your data.<\/p>\n\n\n\n

Here’s the syntax for defining a default value for a column when creating a new table:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> table_name(\n   id<\/span> INT<\/span> PRIMARY KEY<\/span>,\n   colunm_name datatype DEFAULT<\/span> default_value\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify a default value after the DEFAULT<\/code> keyword. When you insert a row into the table without specifying a value for the column_name, Oracle will use the defafult_value.<\/p>\n\n\n\n
To add a default constraint to an existing column, you can use the ALTER TABLE<\/code> statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name\nMODIFY<\/span> column_name DEFAULT<\/span> default_value;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Oracle DEFAULT constraint examples #<\/a><\/h2>\n\n\n\n
Let’s take some examples of using the DEFAULT<\/code> constraints.<\/p>\n\n\n\n
Setting a fixed default value for a column #<\/a><\/h3>\n\n\n\n
First, create a new table<\/a> called payments<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> payments(\n   id<\/span> NUMBER<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n   customer_id NUMBER<\/span> NOT<\/span> NULL<\/span>,\n   amount NUMBER<\/span>(11<\/span>,2<\/span>) NOT<\/span> NULL<\/span>,\n   status<\/span> VARCHAR2<\/span>(25<\/span>) DEFAULT<\/span> 'Pending'<\/span> NOT<\/span> NULL<\/span>,\n   payment_date DATE<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In the payments table, the status column has a default value ‘Pending’. Note that the NOT NULL<\/code> cconstraint must appear after the DEFAULT<\/code> constraint, or you’ll encounter an error.<\/p>\n\n\n\n
INSERT<\/span> INTO<\/span> payments(customer_id, amount, payment_date)\nVALUES<\/span>(1<\/span>, 21000<\/span>, DATE<\/span> '2025-05-12'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> * FROM<\/span> payments;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"oracle<\/figure>\n\n\n\n
The output indicates that Oracle uses the default value 'Pending'<\/code> to insert into the status<\/code> column.<\/p>\n\n\n\n
Using a DATE function as a default value #<\/a><\/h3>\n\n\n\n
First, add a new column created_at<\/code> to the payments<\/code> table:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> payments\nADD<\/span> COLUMN<\/span> created_at TIMESTAMP<\/span> WITH<\/span> TIME<\/span> ZONE \nDEFAULT<\/span> CURRENT_TIMESTAMP<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, retrieve data from the payments<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> payments;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
You’ll see a new column created_at<\/code> is updated with the time when you run the ALTER TABLE<\/code> column.<\/p>\n\n\n\n
Third, insert a new row into the payments<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> payments(customer_id, amount, payment_date)\nVALUES<\/span>(2<\/span>, 32000<\/span>, DATE<\/span> '2025-05-12'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Since we don’t provide a value for the created_at column, Oraccle will use the timestamp when we execute the INSERT statement.<\/p>\n\n\n\n
Finally, retrieve data from payments<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> payments;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
Notice that you’ll see different values in the created_at<\/code> column.<\/p>\n\n\n\n
\"oracle<\/figure>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use a DEFAULT<\/code> constraint to set a default value for column when no value is provided during an insert.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Third, query data<\/a> from the payments<\/code> table:<\/p>\n\n\n
Second, insert a new row<\/a> into the payments<\/code> table:<\/p>\n\n\n
Note that you can combine the DEFAULT<\/code> constraint with other constraints such as NOT NULL<\/a><\/code>, CHECK<\/a><\/code> and UNIQUE<\/a><\/code>.<\/p>\n\n\n\n