NOT NULL Constraint FAQS

1. What is a NOT NULL constraint in Oracle?

A NOT NULL constraint ensures that a column cannot contain NULL values.

It enforces mandatory data entry and maintains data integrity.

CREATE TABLE employees (

   emp_id NUMBER NOT NULL,

   name   VARCHAR2(100)

);

emp_id must always have a value.

2. Why is the NOT NULL constraint important?

Ensures:

• Mandatory fields are filled
• No incomplete records
• Better data accuracy

Common use cases:

• Primary keys
• Foreign keys
• Required business fields (email, order date)

3. How is NOT NULL different from other constraints?

Feature	NOT NULL	PRIMARY KEY	UNIQUE
Prevents NULL	Yes	Yes	No (one NULL allowed)
Prevents duplicates	No	Yes	Yes
Creates index	No	Yes	Yes
Can have multiple per table	Yes	No	Yes

4. How do you add NOT NULL to an existing column?                         

ALTER TABLE employees

MODIFY emp_id NOT NULL;

• Existing data must not contain NULLs.

5. How do you remove a NOT NULL constraint?

ALTER TABLE employees

MODIFY emp_id NULL;

6. Can NOT NULL be defined at table level?

No. Must be defined at column level.

Example with named constraint:

ALTER TABLE employees

MODIFY emp_id CONSTRAINT emp_id_nn NOT NULL;

7. What happens if you insert NULL into a NOT NULL column?

Oracle raises:

ORA-01400: cannot insert NULL into (...)

INSERT INTO employees (emp_id, name)

VALUES (NULL, 'John');

This fails.

8. Does NOT NULL create an index?

No. Unlike PRIMARY KEY or UNIQUE, NOT NULL does not create an index.

Create an index manually if needed.

9. Is NOT NULL required for PRIMARY KEY?

Yes. PRIMARY KEY automatically applies:

• NOT NULL
• UNIQUE
• Unique index

10. Does NOT NULL affect performance?

Positive: Helps optimizer estimate cardinality and improves execution plans.
Overhead: Minimal during INSERT/UPDATE.
Performance impact is negligible.

11. Can NOT NULL be used with DEFAULT values?

Yes.

CREATE TABLE employees (

   status VARCHAR2(10) DEFAULT 'ACTIVE' NOT NULL

);

Ensures column never becomes NULL; default value is used if none provided.

12. NOT NULL vs CHECK (column IS NOT NULL)

CHECK (emp_id IS NOT NULL)

• NOT NULL is more efficient
• CHECK is slightly slower
• Prefer NOT NULL for mandatory columns

13. How to check NOT NULL columns?

SELECT column_name, nullable

FROM user_tab_columns

WHERE table_name = 'EMPLOYEES';

• NULLABLE = 'N' → NOT NULL
• NULLABLE = 'Y' → NULL allowed

14. Common mistakes

• Adding NOT NULL without cleaning existing NULL data
• Assuming it creates an index
• Forgetting mandatory columns
• Using CHECK instead of NOT NULL

15. When should you use NOT NULL?

Use for:

• Primary key columns
• Foreign key columns
• Mandatory business fields
• Audit columns
• Status columns

Avoid if data is optional or populated later.

16. Best Practices

• Define NOT NULL for mandatory fields
• Combine with DEFAULT when appropriate
• Use meaningful constraint names
• Add during table creation
• Validate data before applying in production

17. Real-world example

Bad design:

CREATE TABLE orders (

   order_id NUMBER,

   order_date DATE

);

Better design:

CREATE TABLE orders (

   order_id NUMBER PRIMARY KEY,

   order_date DATE NOT NULL

);

Ensures no missing IDs or order dates.

18. Interview Tip

“NOT NULL ensures a column cannot contain NULL values. It enforces mandatory data entry, is defined at column level, does not create an index, has minimal performance impact, and is automatically applied to PRIMARY KEY columns. Use NOT NULL for all mandatory business fields.”