DEFAULT Constraint FAQS

1. What is a DEFAULT constraint in Oracle?

A DEFAULT constraint automatically assigns a default value to a column when no value is specified during an INSERT.

·        Ensures consistent initial values

·        Reduces application-side NULL checks

·        Often used with optional columns

Example:

CREATE TABLE employees (

    emp_id NUMBER PRIMARY KEY,

    name VARCHAR2(100),

    status VARCHAR2(10) DEFAULT 'ACTIVE'

);

·        If status is not provided, it defaults to 'ACTIVE'.

2. What problem does a DEFAULT constraint solve?

·        Avoids NULL values in optional columns

·        Simplifies data insertion

·        Ensures business rules automatically

3. Can DEFAULT values be expressions?

Yes. Oracle supports constants, functions, or expressions (12c+).

-- Constant default

salary NUMBER DEFAULT 1000

-- Using a function

created_date DATE DEFAULT SYSDATE

-- Expression (12c+ with virtual columns)

bonus NUMBER DEFAULT salary * 0.1

4. Can DEFAULT reference another column?

No. Default values cannot reference other columns, except via virtual/generated columns:

salary_with_bonus NUMBER GENERATED ALWAYS AS (salary * 1.1) VIRTUAL

5. When is DEFAULT applied?

·        Only when INSERT does not provide a value

·        Explicit values override default

Example:

INSERT INTO employees (emp_id, name) VALUES (1, 'John');

-- status = 'ACTIVE'

INSERT INTO employees (emp_id, name, status) VALUES (2, 'Jane', 'INACTIVE');

-- status = 'INACTIVE'

6. Can DEFAULT columns allow NULLs?

Yes, unless combined with NOT NULL:

status VARCHAR2(10) DEFAULT 'ACTIVE' NOT NULL

7. How to add DEFAULT to an existing table?

ALTER TABLE employees

MODIFY status DEFAULT 'ACTIVE';

·        Existing rows are not updated. Only new inserts get the default.

8. How to remove a DEFAULT?

ALTER TABLE employees

MODIFY status DEFAULT NULL;

9. Can DEFAULT values be system functions?

Yes. Oracle allows deterministic functions:

created_by VARCHAR2(30) DEFAULT USER

created_date DATE DEFAULT SYSDATE

10. Can DEFAULT be used with sequences?

·        Not directly. Use triggers for auto-populating IDs:

CREATE OR REPLACE TRIGGER emp_before_insert

BEFORE INSERT ON employees

FOR EACH ROW

BEGIN

   IF :NEW.emp_id IS NULL THEN

      :NEW.emp_id := emp_seq.NEXTVAL;

   END IF;

END;

11. Viewing DEFAULT constraints

SELECT column_name, data_default

FROM user_tab_columns

WHERE table_name = 'EMPLOYEES';

·        data_default shows the default value.

12. Common mistakes

·        Expecting existing rows to update → they don’t

·        Trying to use sequences or other columns → Not allowed

·        Forgetting NOT NULL when value must exist

·        Using complex expressions on versions <12c → may fail

13. Performance impact

·        Minimal overhead, evaluated at insert only

·        Functions like SYSDATE have negligible cost

14. Best practices

·        Name columns meaningfully and provide default if optional

·        Combine DEFAULT with NOT NULL for mandatory fields

·        Use simple constants or deterministic functions

·        For IDs, use sequences + triggers instead of DEFAULT

·        Document defaults for consistent behavior

15. Real-world examples

1.    Default status:

status VARCHAR2(10) DEFAULT 'ACTIVE' NOT NULL

2.    Default creation timestamp:

created_date DATE DEFAULT SYSDATE

3.    Default role:

role VARCHAR2(20) DEFAULT 'USER'

16. Interview Tip

“A DEFAULT constraint assigns a predefined value to a column when no value is supplied during insert. It ensures consistent initial values, reduces application validation, and can use constants or system functions like SYSDATE or USER. For mandatory fields, combine with NOT NULL; for sequences, use triggers instead of DEFAULT.”