Oracle SQL DDL: CREATE TABLE, Data Types and Constraints

Oracle SQL DDL: CREATE TABLE and Constraints – Complete Guide

The CREATE TABLE statement is the foundation of every Oracle database. It defines the structure of your tables, the data types for each column, and the rules (constraints) that protect the integrity of your data.

Learning Objectives
By the end of this guide you will know how to create tables, choose the right data types, define all five constraint types, understand column-level vs table-level syntax, and use CREATE TABLE AS SELECT effectively.

1. CREATE TABLE – Basic Syntax

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)
);

2. Common Oracle Data Types

Data Type	What it stores	Key points
VARCHAR2(n)	Variable-length character string	Maximum length n is required
CHAR(n)	Fixed-length character string	Shorter values are padded with spaces
NUMBER(p,s)	Numbers with precision and scale	Both p and s are optional
DATE	Date and time (including time of day)	Always stores hours, minutes, seconds
TIMESTAMP	Date with fractional seconds	Higher precision than DATE

3. The Five Constraint Types

Constraint	Enforces	Allows NULL?
NOT NULL	Column must contain a value	No
PRIMARY KEY	Unique + NOT NULL identifier	No
UNIQUE	No duplicate values	Yes (multiple NULLs allowed)
FOREIGN KEY	Value must exist in referenced table	Yes
CHECK	Value must satisfy a condition	Yes (NULL passes CHECK)

4. Column-Level vs Table-Level Constraints

Most constraints can be defined either inline with the column or at the table level after all columns are listed. NOT NULL must be column-level only.

-- Column-level (inline)
employee_id NUMBER(6) CONSTRAINT emp_id_pk PRIMARY KEY,

-- Table-level (after columns)
CONSTRAINT emp_id_pk PRIMARY KEY (employee_id)

5. NOT NULL Constraint

```sql last_name VARCHAR2(25) NOT NULL