DEFAULT Constraint

A DEFAULT constraint in Oracle is used to assign a default value to a column when no explicit value is provided during the insertion of a record. This ensures that even if a user does not provide data for a column, Oracle automatically populates it with the default value specified in the table definition.

Here’s a comprehensive overview of how the DEFAULT constraint works in Oracle:

1. Purpose of DEFAULT Constraint

·        The DEFAULT constraint automatically provides a value for a column when an insert operation does not include a value for that column.

·        This helps maintain data consistency and can prevent errors when inserting records without providing values for certain columns.

2. Syntax of the DEFAULT Constraint

You can specify the DEFAULT value during table creation or alter an existing table.

Creating a Table with DEFAULT

CREATE TABLE employees (

    employee_id NUMBER PRIMARY KEY,

    first_name VARCHAR2(50),

    last_name VARCHAR2(50),

    hire_date DATE DEFAULT SYSDATE,  -- Default value is the current system date

    salary NUMBER DEFAULT 50000,     -- Default value is 50000

    department_id NUMBER DEFAULT 10  -- Default value is 10

);

Modifying a Table to Add a DEFAULT Constraint

ALTER TABLE employees

ADD CONSTRAINT df_salary DEFAULT 50000 FOR salary;

·        df_salary: Name of the constraint.

·        DEFAULT 50000: Default value being set.

·        FOR salary: The column that will have the default value.

 

3. Types of Values Allowed as Default

·        Static Values: You can set default values that are constant, like numbers, strings, or dates.

o   Example: DEFAULT 50000, DEFAULT 'Unknown', or DEFAULT SYSDATE.

·        Functions or Expressions: You can also assign default values using Oracle functions or expressions.

o   Example: DEFAULT SYSDATE (sets the default value to the current system date and time).

o   Example: DEFAULT USER (sets the default value to the current database user).

o   Example: DEFAULT (salary * 1.1) (sets a default value based on another column, though this would typically be handled better with triggers).

 

4. Default Values for Data Types

·        Numbers: Default values can be integers or floating-point numbers. For example, DEFAULT 100, DEFAULT 3.14.

·        Strings: Default values can be string literals. For example, DEFAULT 'New Hire'.

·        Dates: Default values can be date literals or system date functions like SYSDATE or CURRENT_DATE.

o   Example: DEFAULT TO_DATE('2025-01-01', 'YYYY-MM-DD').

·        Boolean (in some cases): While Oracle doesn't have a native BOOLEAN datatype, you can use CHAR or NUMBER to simulate boolean values.

o   Example: DEFAULT 'Y' or DEFAULT 1 for TRUE, and DEFAULT 'N' or DEFAULT 0 for FALSE.

 

5. How the DEFAULT Constraint Works

·        When No Value Is Provided: If an insert operation doesn't specify a value for a column that has a DEFAULT constraint, Oracle automatically inserts the default value.

INSERT INTO employees (employee_id, first_name, last_name)

VALUES (1, 'John', 'Doe');

o   In the example above, if the hire_date, salary, and department_id columns have default values, Oracle will insert the default values (e.g., SYSDATE, 50000, and 10 respectively) for those columns.

·        When a Value Is Provided: If a value is explicitly provided in the insert statement, Oracle will use that value and ignore the default.

INSERT INTO employees (employee_id, first_name, last_name, salary)

VALUES (2, 'Jane', 'Smith', 60000);

o   In this case, the salary will be 60000 (not the default 50000).

·        Nullability: If a column has a DEFAULT constraint, Oracle will allow NULL as a value for the column unless specified otherwise by a NOT NULL constraint.

CREATE TABLE employees (

    employee_id NUMBER PRIMARY KEY,

    first_name VARCHAR2(50),

    last_name VARCHAR2(50),

    hire_date DATE DEFAULT SYSDATE NOT NULL  -- Cannot insert NULL

);

 

6. Important Points to Remember

·        Default values are only used when no explicit value is provided during an insert. If a column is left out in the insert statement, Oracle will use the default.

·        Default values are not applied when inserting NULL. If a column allows NULL values and a NULL is inserted, the default value will not be used. Instead, the column will remain NULL.

·        INSERT INTO employees (employee_id, first_name, last_name, salary)

·        VALUES (3, 'Mike', 'Johnson', NULL); -- Will insert NULL for salary, not 50000

·        Altering Default Value: You can change the default value for an existing column using the ALTER statement, but removing or modifying a default constraint may require a drop and re-add process.

·        ALTER TABLE employees

·        MODIFY salary DEFAULT 55000;  -- Change the default value

·        System Defaults: Oracle has several system-generated default values like SYSDATE, CURRENT_DATE, and USER, which are frequently used for automatic time/date and user-related defaults.

·        Non-Nullable Columns: When using DEFAULT in non-nullable columns (i.e., NOT NULL constraint), the column will always have a value, either from the DEFAULT or from the provided value in an insert.

 

7. Example Use Case for DEFAULT Constraint

Imagine an e-commerce application where every new order must have a default status of "pending" unless specified otherwise. The DEFAULT constraint can be used to automatically populate the status column.

CREATE TABLE orders (

    order_id NUMBER PRIMARY KEY,

    customer_id NUMBER NOT NULL,

    order_date DATE DEFAULT SYSDATE,

    status VARCHAR2(20) DEFAULT 'Pending'

);

Now, when an order is inserted without specifying a status:

INSERT INTO orders (order_id, customer_id)

VALUES (101, 10);

·        The order_date will default to the current date, and the status will default to "Pending".

 

8. Common Pitfalls and Limitations

·        No Default on NOT NULL Constraint: The DEFAULT constraint cannot override a NOT NULL column unless the column is explicitly set to NOT NULL and DEFAULT at the same time.

·        Triggers vs. DEFAULT: Complex defaults, such as those dependent on other columns, should be handled with triggers instead of using the DEFAULT constraint.

 

9. Conclusion

The DEFAULT constraint in Oracle provides a simple yet powerful mechanism for setting default values on columns. It is commonly used to ensure data integrity and consistency without requiring users to always provide a value for every column. However, it is important to understand its limitations, especially when combined with other constraints like NOT NULL, and to use it appropriately in the context of business rules.