Managing Schema Objects
Scope
This domain tests tables, constraints, indexes, views, sequences, and synonyms. Questions often ask which object enforces a rule or why a DML statement fails.
Core rules
- Heap tables store rows in no particular order; partitioned tables split data into partitions; external tables read files through a driver. Constraints: NOT NULL, CHECK, PRIMARY KEY, UNIQUE, FOREIGN KEY. PRIMARY KEY implies NOT NULL and UNIQUE.
- Constraints can be ENABLED/DISABLED and VALIDATED/NOVALIDATED. Deferrable constraints can be checked at commit; NOT NULL cannot be deferrable.
- B-tree indexes suit selective equality and range predicates; bitmap indexes suit low-cardinality columns and are poor for heavy DML; function-based indexes require a matching expression. Local indexes are partitioned with the table; global indexes are not.
- Sequences generate numbers with CACHE, NOCACHE, CYCLE, and ORDER options. Gaps are normal after rollback or cache loss. Views can use WITH CHECK OPTION; synonyms are aliases.
Worked example
CREATE TABLE hr.t (id NUMBER PRIMARY KEY, email VARCHAR2(100) UNIQUE, dept_id NUMBER REFERENCES hr.dept(dept_id)); CREATE SEQUENCE hr.t_seq START WITH 100 CACHE 20; INSERT INTO hr.t VALUES (hr.t_seq.NEXTVAL, 'a@x', 10); A duplicate email violates the unique constraint; a missing dept_id violates the foreign key.
Traps
UNIQUE allows multiple NULLs. NOT NULL is not deferrable. A bitmap index can block concurrent DML. A sequence gap is not corruption. A view with CHECK OPTION rejects rows that would disappear from the view.