1Z0-071 Exam Prep
Chapter 11
Volume 3
DDL — Data Definition Language — covers the statements that create, modify, and remove database objects. On the 1Z0-071 exam, CREATE TABLE is the most heavily tested DDL topic. The questions focus on data type choices, constraint syntax, the difference between column-level and table-level constraints, and what each constraint type actually enforces.
This post covers everything you need to know about CREATE TABLE and constraints for the exam.
In this post
01 — CREATE TABLE — basic syntax
The CREATE TABLE statement defines a new table, its columns, data types, and any constraints. The basic structure is straightforward — the exam focuses on the rules, not the typing.
CREATE TABLE departments ( department_id NUMBER(4) CONSTRAINT dept_id_pk PRIMARY KEY, department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL, manager_id NUMBER(6), location_id NUMBER(4) );
Naming rules: Table names and column names must start with a letter, be 1–30 characters, contain only letters, numbers, underscores, dollar signs, or hash symbols, and must not duplicate another object name owned by the same user.
02 — Oracle data types tested on the exam
| Data type | What it stores | Key exam point |
|---|---|---|
VARCHAR2(n) | Variable-length character string, max n bytes | n is required — VARCHAR2 alone raises an error |
CHAR(n) | Fixed-length character string, always n bytes | Shorter values are padded with spaces to fill n bytes |
NUMBER(p,s) | Numeric value, p digits total, s after decimal | Both p and s are optional — NUMBER alone is valid |
DATE | Date and time (century, year, month, day, hour, minute, second) | Always stores time — not just the date portion |
TIMESTAMP | DATE plus fractional seconds | More precision than DATE for time values |
CLOB | Character large object — up to 128TB | Used for large text; some restrictions on use in constraints |
BLOB | Binary large object — up to 128TB | Used for binary data like images or documents |
VARCHAR2 requires a length:
VARCHAR2 without a size specification raises ORA-00906. CHAR without a size defaults to CHAR(1) — valid but often unintentional. The exam tests this distinction.
-- CHAR vs VARCHAR2 — the padding difference CREATE TABLE test_types ( fixed_col CHAR(10), -- stores 'Hi' as 'Hi ' (8 trailing spaces) var_col VARCHAR2(10) -- stores 'Hi' as 'Hi' (2 bytes only) ); -- NUMBER precision and scale NUMBER(7, 2) -- up to 7 digits total, 2 after decimal — e.g. 12345.67 NUMBER(5) -- integer up to 5 digits — scale defaults to 0 NUMBER -- floating point — no fixed precision limit
03 — The five constraint types
Oracle has five constraint types. The exam tests all of them — what each one enforces, where it can be defined, and what happens when it is violated.
| Constraint | Enforces | Allows NULL? | Per table limit |
|---|---|---|---|
NOT NULL | Column must have a value | No — that is the point | One per column |
PRIMARY KEY | Unique, non-null identifier for each row | No | One per table |
UNIQUE | No duplicate values in the column(s) | Yes — multiple NULLs allowed | Multiple allowed |
FOREIGN KEY | Value must exist in the referenced parent table | Yes — NULL FK is allowed | Multiple allowed |
CHECK | Value must satisfy a specified condition | Yes — NULL passes CHECK | Multiple allowed |
04 — Column-level vs table-level constraint syntax
Constraints can be defined at the column level (inline with the column definition) or at the table level (after all columns are listed). Most constraints can go either place — with one exception.
-- Column-level constraints (defined inline) CREATE TABLE employees ( employee_id NUMBER(6) CONSTRAINT emp_id_pk PRIMARY KEY, last_name VARCHAR2(25) CONSTRAINT emp_ln_nn NOT NULL, email VARCHAR2(25) CONSTRAINT emp_em_uk UNIQUE, department_id NUMBER(4) CONSTRAINT emp_dept_fk REFERENCES departments(department_id) ); -- Table-level constraints (defined after all columns) CREATE TABLE employees ( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), department_id NUMBER(4), CONSTRAINT emp_id_pk PRIMARY KEY (employee_id), CONSTRAINT emp_em_uk UNIQUE (email), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id) );
NOT NULL cannot be table-level: NOT NULL is the only constraint that must be defined at the column level. You cannot write
CONSTRAINT col_nn NOT NULL (column_name) at the table level — it raises an error. The exam tests this rule directly.
05 — NOT NULL constraint
NOT NULL prevents a column from holding a NULL value. Any INSERT or UPDATE that would leave the column empty raises an error.
-- Column-level only — no table-level syntax exists for NOT NULL CREATE TABLE orders ( order_id NUMBER NOT NULL, order_date DATE NOT NULL, customer_id NUMBER, -- NULL allowed here status VARCHAR2(20) NOT NULL ); -- Attempting to insert without a NOT NULL column raises ORA-01400 INSERT INTO orders (order_id, order_date) VALUES (1, SYSDATE); -- Error: ORA-01400: cannot insert NULL into ("STATUS")
06 — PRIMARY KEY constraint
A primary key uniquely identifies each row in a table. It combines the behaviour of UNIQUE and NOT NULL. A table can have only one primary key, but that key can span multiple columns (composite primary key).
-- Single-column primary key CREATE TABLE departments ( department_id NUMBER(4) PRIMARY KEY, department_name VARCHAR2(30) ); -- Composite primary key — must be table-level CREATE TABLE order_items ( order_id NUMBER(6), item_id NUMBER(6), quantity NUMBER(4), CONSTRAINT oi_pk PRIMARY KEY (order_id, item_id) );
Composite key rule: When a primary key spans more than one column it must be defined at the table level — not inline with either column. The exam tests this. A composite primary key means the combination of values must be unique, not each column independently.
07 — UNIQUE constraint
UNIQUE enforces that no two rows share the same value in the constrained column. Unlike PRIMARY KEY, UNIQUE allows NULL values — and importantly, multiple NULLs are allowed because NULL is not equal to NULL.
CREATE TABLE employees ( employee_id NUMBER(6) PRIMARY KEY, email VARCHAR2(25) UNIQUE, -- no two employees share an email phone VARCHAR2(20) -- NULLs allowed, no UNIQUE here ); -- These two rows are both valid despite the same phone being NULL INSERT INTO employees VALUES (1, 'a@x.com', NULL); INSERT INTO employees VALUES (2, 'b@x.com', NULL); -- Both rows allowed — NULL is not considered a duplicate value
UNIQUE vs PRIMARY KEY on NULLs: PRIMARY KEY rejects NULL entirely. UNIQUE allows NULL — and allows multiple NULLs in the same column. The exam frequently uses this distinction in questions that ask which constraint is appropriate for a given column requirement.
08 — FOREIGN KEY constraint
A foreign key links a column in one table (the child) to the primary key or unique key of another table (the parent). Oracle enforces that every non-NULL foreign key value must exist in the parent table.
CREATE TABLE employees ( employee_id NUMBER(6) PRIMARY KEY, last_name VARCHAR2(25) NOT NULL, department_id NUMBER(4), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE SET NULL -- when parent row deleted, set FK to NULL ); -- ON DELETE CASCADE: deleting the parent also deletes the child rows CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE
| Delete option | What happens when parent row is deleted |
|---|---|
| No option (default) | Delete rejected — ORA-02292 if child rows exist |
ON DELETE SET NULL | Child FK column set to NULL |
ON DELETE CASCADE | Child rows are deleted automatically |
ON UPDATE does not exist in Oracle: Oracle does not support
ON UPDATE CASCADE or ON UPDATE SET NULL. If you see these in an exam answer option they are wrong. Only the ON DELETE variants exist.
09 — CHECK constraint
CHECK enforces that values in a column satisfy a specific condition. The condition can reference any column in the same row but cannot reference other tables, sequences, or non-deterministic functions like SYSDATE or USER.
CREATE TABLE employees ( employee_id NUMBER(6) PRIMARY KEY, salary NUMBER(8,2) CONSTRAINT emp_sal_ck CHECK (salary > 0), gender CHAR(1) CONSTRAINT emp_gen_ck CHECK (gender IN ('M', 'F')), hire_date DATE, end_date DATE, -- Multi-column check — must be table-level CONSTRAINT emp_dates_ck CHECK (end_date >= hire_date) ); -- WRONG: CHECK cannot reference SYSDATE CONSTRAINT emp_hire_ck CHECK (hire_date <= SYSDATE) -- Error: date functions like SYSDATE not allowed in CHECK
NULL passes CHECK: If the column value is NULL, the CHECK condition evaluates to UNKNOWN — and Oracle does not reject UNKNOWN. A NULL value will always pass a CHECK constraint. To prevent NULL you need a separate NOT NULL constraint.
10 — CREATE TABLE AS SELECT
You can create a new table and populate it with data in one step using CREATE TABLE AS SELECT (CTAS). The new table inherits the column names and data types from the query — but not constraints.
-- Create a table with structure and data from a query CREATE TABLE emp_copy AS SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id = 50; -- Creates emp_copy with the 4 columns and only dept 50 rows -- Create an empty copy (structure only, no rows) CREATE TABLE emp_empty AS SELECT * FROM employees WHERE 1 = 2; -- WHERE 1=2 is always false — creates the table structure with zero rows
Constraints are not copied: CTAS copies column names and data types only. NOT NULL constraints from the source table are preserved, but PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK constraints are not. You must add them separately after the table is created.
11 — Quick reference
| Rule | Detail |
|---|---|
| VARCHAR2 requires size | VARCHAR2 alone raises ORA-00906 |
| CHAR pads with spaces | CHAR(10) storing 'Hi' stores 'Hi ' |
| DATE stores time too | Always includes hour, minute, second |
| NOT NULL — column-level only | Cannot be defined at the table level |
| PRIMARY KEY — one per table | Enforces UNIQUE + NOT NULL |
| Composite PK — table-level only | Multi-column PKs cannot be inline |
| UNIQUE allows NULLs | Multiple NULLs are allowed in a UNIQUE column |
| FOREIGN KEY allows NULL | NULL FK value is valid — skips parent check |
| ON DELETE CASCADE / SET NULL | ON UPDATE does not exist in Oracle |
| CHECK rejects SYSDATE/USER | Non-deterministic functions not allowed in CHECK |
| NULL passes CHECK | UNKNOWN result is not rejected — add NOT NULL separately |
| CTAS — constraints not copied | Only NOT NULL is preserved; others must be added manually |
12 — Practice questions
Q1 — A table needs a two-column primary key on (order_id, item_id). Which syntax is correct?
Q2 — A SALARY column has a CHECK constraint:
CHECK (salary > 0). A row is inserted with a NULL salary. What happens?Q3 — A developer creates a copy of the EMPLOYEES table using CTAS. Which of these is true about the new table?
Oracle SQL 1Z0-071 Exam Prep Guide — Complete 4-Volume Set
19 chapters · 1,480 practice questions · 1,249 SQL examples
Chapter 11 covers the full DDL lifecycle — CREATE, ALTER, DROP, TRUNCATE — with practice sets for every constraint type and scenario. Instant PDF download.
Get the full guide — $25 →
Chapter 11 covers the full DDL lifecycle — CREATE, ALTER, DROP, TRUNCATE — with practice sets for every constraint type and scenario. Instant PDF download.
No comments:
Post a Comment