Data Definition Language (DDL) and Schema Objects
Domain 9 tests creating and changing schema objects with DDL: tables, constraints, and related structures. The exam expects you to know data types, constraint behavior, and which operations commit implicitly.
Core rules
- CREATE TABLE defines columns and optionally constraints. Common constraints are NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK.
- A primary key is unique and not null. A foreign key references a primary or unique key and enforces referential integrity. CHECK limits values by a condition.
- ALTER TABLE can add, modify, drop, or rename columns and add or drop constraints. Some changes fail when data violates the new rule.
- DROP TABLE removes the table and its data. TRUNCATE TABLE removes all rows quickly and leaves the structure. Both are DDL and implicitly commit.
- CREATE TABLE AS SELECT creates a table from a query result. It does not automatically copy indexes, triggers, or all constraints, so recreate needed objects.
- VARCHAR2 stores variable-length character data; CHAR stores fixed-length blank-padded data. NUMBER stores numeric values with optional precision and scale.
Worked example
CREATE TABLE project_emp (emp_id NUMBER(6) PRIMARY KEY, project_id NUMBER(6) NOT NULL, role VARCHAR2(30), CONSTRAINT ck_role CHECK (role IN ('DEV','QA','PM')));
This creates three columns. emp_id is unique and not null because of PRIMARY KEY. project_id cannot be NULL. role is limited by the CHECK constraint to three values or NULL, since NULL passes a CHECK that evaluates to unknown. DDL commits implicitly.
Traps
TRUNCATE cannot be rolled back like DELETE. A CHECK constraint does not reject NULL unless NOT NULL is also applied. Dropping a parent table can fail if foreign keys reference it. CTAS may leave you without expected constraints or indexes. ALTER TABLE MODIFY can fail if existing data violates the new definition.
Key points
- PRIMARY KEY means unique plus not null.
- FOREIGN KEY enforces referential integrity.
- TRUNCATE is DDL and commits implicitly.
- CTAS does not copy all dependent objects.
- CHECK allows NULL unless combined with NOT NULL.