Data Definition Language: Tables, Constraints, and Views
Data Definition Language: Tables, Constraints, and Views
This domain tests creating and altering tables, enforcing constraints, and defining views. DDL statements change object definitions and commit automatically.
Core rules
- CREATE TABLE defines columns, data types, defaults, and constraints. Constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK.
- PRIMARY KEY implies UNIQUE and NOT NULL. A FOREIGN KEY references a primary or unique key and can specify ON DELETE CASCADE or ON DELETE SET NULL.
- ALTER TABLE adds, modifies, or drops columns and constraints. Adding NOT NULL to a column with existing NULLs fails unless values are supplied.
- DROP TABLE removes the table and its data; TRUNCATE TABLE removes all rows but keeps the definition.
- A simple view is based on one table without functions or grouping and is often updatable. A complex view may be read-only.
- WITH CHECK OPTION prevents INSERT or UPDATE through a view if the row would not satisfy the view’s WHERE clause.
Worked example
CREATE TABLE project ( project_id NUMBER CONSTRAINT project_pk PRIMARY KEY, name VARCHAR2(100) NOT NULL, budget NUMBER CHECK (budget >= 0), dept_id NUMBER CONSTRAINT project_dept_fk REFERENCES departments(dept_id) ON DELETE SET NULL ); The primary key enforces unique, non-NULL IDs; the check rejects negative budgets; deleting a department sets project.dept_id to NULL instead of deleting projects.
Traps
- Expecting TRUNCATE to be rollbackable.
- Dropping a parent table while enabled foreign keys reference it.
- Assuming every view is updatable.
- Forgetting that WITH CHECK OPTION affects DML through the view.