Specifying Column Default Values

Specifying default values for columns in an Oracle Database is an important method for ensuring efficient data entry and data consistency. This article provides a comprehensive explanation of how to specify default values, key considerations, and best practices.

How to Specify Default Values
Examples of Using INSERT Statements
Considerations and Best Practices
[Advanced] Useful Techniques for Leveraging Default Values
Troubleshooting (Common Errors)
Summary

How to Specify Default Values

Default values can be set using the CREATE TABLE statement or the ALTER TABLE statement.

When Creating a New Table

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    hire_date DATE DEFAULT SYSDATE,
    salary NUMBER DEFAULT 50000
);

The hire_date column is set to the current date (SYSDATE) by default.
The salary column is set to 50000 by default.

When Adding Default Values to an Existing Table

ALTER TABLE employees
ADD COLUMN bonus NUMBER DEFAULT 1000;

The newly added bonus column will have 1000 set as its default value.

Changing Default Values

ALTER TABLE employees
MODIFY salary DEFAULT 60000;

The default value of the salary column is changed to 60000.

Removing Default Values

ALTER TABLE employees
MODIFY salary DEFAULT NULL;

The default value of the salary column is removed.

Examples of Using INSERT Statements

Default values are automatically applied when a value is not explicitly specified in an INSERT statement.

Data Insertion Using Default Values

INSERT INTO employees (employee_id, first_name, last_name)
VALUES (1, 'John', 'Doe');

In this case, hire_date is automatically set to the current date, and salary is set to 50000.

SQL> SELECT column_name, data_default
  2  FROM user_tab_columns
  3  WHERE table_name = 'EMPLOYEES';

COLUMN_NAME                    DATA_DEFAULT
------------------------------ ------------------------------
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
HIRE_DATE                      SYSDATE  ★Default value is set
SALARY                         50000    ★

SQL> INSERT INTO employees (employee_id, first_name, last_name)
  2  VALUES (1, 'John', 'Doe');

1 row created.

SQL> select * from employees;

EMPLOYEE_ID FIRST_NAME           LAST_NAME            HIRE_DATE               SALARY
----------- -------------------- -------------------- ------------------- ----------
          1 John                 Doe                  2025/01/13 20:22:59      50000  ★

Overriding Default Values

INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (2, 'Jane', 'Smith', 70000);

In this case, the salary column is set to the explicitly specified 70000 instead of the default value of 50000.

SQL> INSERT INTO employees (employee_id, first_name, last_name, salary)
  2  VALUES (2, 'Jane', 'Smith', 70000);

1 row created.

SQL> select * from employees;

EMPLOYEE_ID FIRST_NAME           LAST_NAME            HIRE_DATE               SALARY
----------- -------------------- -------------------- ------------------- ----------
          1 John                 Doe                  2025/01/13 20:22:59      50000
          2 Jane                 Smith                2025/01/13 20:32:40      70000  ★

Verifying Default Value Behavior

SELECT employee_id, first_name, last_name, hire_date, salary
FROM employees;

You can confirm from the execution results that the default values are applied correctly.

Considerations and Best Practices

Data Types That Can Be Specified as Default Values

Literal values (e.g., Numbers, Strings, NULL)
System functions (e.g., SYSDATE, USER)
Arithmetic expressions or string concatenation (e.g., 10 * 2, 'default_' || 'value')

Combining Default Values with NOT NULL Constraints

By setting a NOT NULL constraint on a column with a default value, the default value takes on a mandatory meaning.

CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, department_name VARCHAR2(100) NOT NULL DEFAULT 'General' );

The department_name column will always have a value set. If no value is specified, 'General' is set.

Impact on Existing Data

Default values are applied to newly inserted rows.
Values in existing rows are not changed.

Using Sequences as Default Values (Oracle Database 12c and later)

You can specify the next value of a sequence as a default value.

CREATE TABLE projects ( project_id NUMBER DEFAULT project_seq.NEXTVAL PRIMARY KEY, project_name VARCHAR2(100) );

When inserting a new project, project_id is automatically generated from the sequence.

Impact on Performance

If the default value depends on complex calculations or functions, it may impact the performance of insert operations.

How to Check Default Values

To check if a default value is set, use the data dictionary views.

Using the USER_TAB_COLUMNS View

SELECT column_name, data_default
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';

The data_default column displays the specified default value.

Specific Example

As an example, the result of checking the default values for the employees table is as follows:

COLUMN_NAME      DATA_DEFAULT
HIRE_DATE        SYSDATE
SALARY           50000
BONUS            1000

Specific Use Cases

Setting Initial Values: Automatically setting standard salaries or bonuses for new employees.
Automatic Date Setting: Automatically recording a timestamp when a record is inserted.
Ensuring Consistency: Using default values to prevent mistakes or inconsistencies during data entry.

[Advanced] Useful Techniques for Leveraging Default Values

In Oracle Database 12c and later (including 19c), the functionality of default values has been enhanced. Here, we introduce features that aid in more robust and efficient database design.

1. Preventing Explicit NULL Insertions with DEFAULT ON NULL

With a normal DEFAULT setting, if NULL is explicitly specified in an INSERT statement, the default value is not applied, and NULL is registered.

Some application frameworks may automatically bind NULL when there is no value. To prevent this, use the DEFAULT ON NULL clause.

-- Example using DEFAULT ON NULL
CREATE TABLE user_settings (
    setting_id NUMBER PRIMARY KEY,
    theme_color VARCHAR2(20) DEFAULT ON NULL 'BLUE' -- Sets 'BLUE' even if NULL comes in
);

-- INSERT explicitly specifying NULL
INSERT INTO user_settings (setting_id, theme_color) VALUES (1, NULL);

-- Check: The default value is entered instead of NULL
SELECT * FROM user_settings;

SETTING_ID THEME_COLOR
---------- --------------------
         1 BLUE

Point: By using this in conjunction with a NOT NULL constraint, you can strongly guarantee data integrity.

2. Using the DEFAULT Keyword in SQL Statements

You can directly describe the keyword DEFAULT instead of a value in INSERT or UPDATE statements. This is very useful when “you want to change the value but don’t want to hard-code the specific default value in SQL (leaving it to the DB definition).”

-- Use in INSERT statement: Apply default value only to specific columns
INSERT INTO employees (employee_id, first_name, salary)
VALUES (101, 'Mike', DEFAULT);

-- Use in UPDATE statement: Reset value to default value
UPDATE employees
SET salary = DEFAULT
WHERE employee_id = 101;

3. Performance When Adding Columns (Metadata-only Default)

While we explained earlier that “Values in existing rows are not changed,” we will provide a detailed supplement regarding behavior in Oracle 12c and later.

When adding a column with a default value (ALTER TABLE … ADD), Oracle does not physically update all rows in the table but updates only the data dictionary (metadata).

Merit: Even for huge tables with hundreds of millions of rows, adding a column with a default value completes instantly. No disk I/O occurs.
Behavior: Although physical data is not rewritten, when SELECT is executed, Oracle automatically references the metadata and returns results as if the existing rows contain the default value.
Note: In Oracle 11g and earlier, adding a default value without a NOT NULL constraint caused a full row update process (generating locks and massive REDO logs). This risk has been resolved in newer versions like 19c.

Troubleshooting (Common Errors)

Here are common errors encountered when handling default values and how to deal with them.

Error Code	Cause	Solution
ORA-01401	Inserted value too large for column	The number of digits or bytes of the default value exceeds the column definition size. Extend the column size or correct the default value to be shorter. `ALTER TABLE tab MODIFY col DEFAULT 'short_val';`
ORA-01722	Invalid number	A string that cannot be converted to a number was specified as the default value for a numeric column. Match the data type of the default value with the column definition. Example: Cannot specify `'ABC'` for a `NUMBER` column.
ORA-32578	DEFAULT ON NULL is valid only with NOT NULL constraint	(Some versions/situations) `DEFAULT ON NULL` implies `NOT NULL`, but this occurs if definitions are contradictory. When using `DEFAULT ON NULL`, reconfirm that the column is designed not to allow NULLs.

Summary

By leveraging column default values, you can significantly improve database usability and operational efficiency. This article covered everything from basic configuration methods to advanced examples. Aim for more efficient database operations by setting appropriate default values.

If you have any questions or additional inquiries, please feel free to comment!

[reference]
Oracle Database SQL Language Reference, 19c