Using DDL Statements to Create and Manage Tables
What the domain covers
DDL creates and manages tables. This domain tests CREATE TABLE, data types, constraints, ALTER TABLE, DROP, TRUNCATE, and defaults.
Core rules
- CREATE TABLE defines columns with names and data types such as NUMBER, VARCHAR2, DATE, and TIMESTAMP. VARCHAR2 length is in bytes by default unless character semantics are specified.
- Constraints enforce rules: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK. PRIMARY KEY implies NOT NULL and UNIQUE. FOREIGN KEY references a primary or unique key.
- Name constraints explicitly for easier maintenance; otherwise Oracle generates names.
- DEFAULT supplies a value when the column is omitted from INSERT. It does not prevent an explicit NULL.
- ALTER TABLE can ADD columns, MODIFY columns, DROP columns, and add or drop constraints. Some modifications are restricted when data exists.
- DROP TABLE removes the table and its data; TRUNCATE removes all rows quickly and leaves the structure. Both are DDL and cannot be rolled back in ordinary operation.
Worked example
CREATE TABLE emp2 (id NUMBER(6) CONSTRAINT emp2_pk PRIMARY KEY, name VARCHAR2(30) NOT NULL, dept_id NUMBER(4) CONSTRAINT emp2_fk REFERENCES departments(department_id));
Reasoning: id is the primary key, so it must be unique and non-null. name is mandatory. dept_id must match an existing department_id in departments or be NULL; foreign keys allow NULL unless also declared NOT NULL. The constraint names make later ALTER TABLE ... DROP CONSTRAINT statements precise.
Traps
NOT NULL cannot be written as a table-level constraint in Oracle; define it at column level. Dropping a parent table referenced by a foreign key fails unless you use CASCADE CONSTRAINTS. TRUNCATE is not DML and cannot be rolled back. A DEFAULT does not override an explicit NULL. Adding a NOT NULL column to a populated table requires a default or prior data handling.