Constraints FAQS

1. What are constraints in Oracle?

Constraints are rules enforced on table columns to maintain data integrity and consistency.

They ensure that:

• Invalid data is not inserted
• Relationships between tables are maintained
• Duplicate or null values are controlled

Constraints are defined during CREATE TABLE or added later using ALTER TABLE.

2. What are the types of constraints in Oracle?

Oracle supports five main types:

Constraint Type	Purpose
NOT NULL	Prevents NULL values
UNIQUE	Prevents duplicate values
PRIMARY KEY	Uniquely identifies each row
FOREIGN KEY	Maintains referential integrity
CHECK	Enforces condition-based validation

3. What is a NOT NULL constraint?

Ensures a column cannot contain NULL values.

CREATE TABLE employees (

   emp_id NUMBER NOT NULL,

   name   VARCHAR2(100)

);

Key points:

• Defined at column level
• Does not create an index
• Mandatory for PRIMARY KEY columns

4. What is a UNIQUE constraint?

Ensures all values in a column (or combination of columns) are unique.

CREATE TABLE employees (

   email VARCHAR2(100) UNIQUE

);

Key points:

• Allows one NULL
• Automatically creates a unique index
• Can be composite

5. What is a PRIMARY KEY constraint?

Uniquely identifies each row.

CREATE TABLE employees (

   emp_id NUMBER PRIMARY KEY

);

Features:

• Automatically NOT NULL and UNIQUE
• Creates a unique index
• Only one per table
• Can be composite: PRIMARY KEY (order_id, product_id)

6. What is a FOREIGN KEY constraint?

Maintains referential integrity between tables.

CREATE TABLE orders (

   order_id NUMBER PRIMARY KEY,

   emp_id   NUMBER,

   CONSTRAINT fk_emp

   FOREIGN KEY (emp_id)

   REFERENCES employees(emp_id)

);

Rules:

• Child value must exist in parent table
• Prevents orphan records
• Does not create an index automatically

7. What is a CHECK constraint?

Validates data based on a condition.

CREATE TABLE employees (

   salary NUMBER CHECK (salary > 0)

);

Can enforce:

• Value ranges
• Specific values
• Logical expressions, e.g., CHECK (status IN ('ACTIVE', 'INACTIVE'))

8. What is a composite constraint?

A constraint on multiple columns, e.g.:

UNIQUE (first_name, last_name)

Ensures uniqueness based on column combinations.

9. PRIMARY KEY vs UNIQUE

Feature	PRIMARY KEY	UNIQUE
Allows NULL	No	Yes (one NULL allowed)
Number per table	One	Multiple
Creates index	Yes	Yes
Identifies row	Yes	No

10. Constraint enforcement timing

• IMMEDIATE (default) → checked after statement
• DEFERRED → checked at COMMIT

ALTER TABLE orders

ADD CONSTRAINT fk_emp

FOREIGN KEY (emp_id)

REFERENCES employees(emp_id)

DEFERRABLE INITIALLY DEFERRED;

11. Constraint states

State	Meaning
ENABLED	Actively enforced
DISABLED	Not enforced
VALIDATE	Existing data checked
NOVALIDATE	Existing data not checked

ALTER TABLE employees DISABLE CONSTRAINT emp_pk;

12. How do constraints affect performance?

Positive:

• Improve query performance (indexes on PK/UNIQUE)
• Help optimizer understand relationships

Negative:

• Slight overhead on INSERT/UPDATE
• Foreign key without index slows DELETE on parent table

Best practice: always index foreign key columns.

13. Can constraints be added after table creation?

Yes.

ALTER TABLE employees

ADD CONSTRAINT emp_email_uk UNIQUE (email);

Drop constraints:

ALTER TABLE employees DROP CONSTRAINT emp_email_uk;

14. What happens if a constraint is violated?

Oracle raises errors:

• ORA-00001 → unique constraint violated
• ORA-02290 → check constraint violated
• ORA-02291 → parent key not found
• ORA-02292 → child record found

15. What is ON DELETE CASCADE?

Used with FOREIGN KEY:

FOREIGN KEY (emp_id)

REFERENCES employees(emp_id)

ON DELETE CASCADE;

• Deletes child rows if parent is deleted
• Alternative: ON DELETE SET NULL

16. Best practices for constraints

• Always define PRIMARY KEY
• Index FOREIGN KEY columns
• Use CHECK constraints for validation
• Use meaningful names
• Avoid disabling constraints in production
• Use DEFERRABLE only when necessary
• Avoid too many constraints in high-volume bulk loads

17. How to view constraints in Oracle?

SELECT constraint_name, constraint_type, table_name

FROM user_constraints;

Types:

• P → Primary key
• R → Foreign key
• U → Unique
• C → Check
• N → Not Null

18. Interview Tip

Constraints are rules to maintain data integrity. Oracle supports NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK. They ensure valid data, maintain relationships, and improve optimizer performance. Proper indexing of foreign keys is critical for performance.