Oracle CREATE TABLE: A Comprehensive Guide with 17 Examples

In Oracle SQL, the CREATE TABLE statement is used to define a new table in the database. A table consists of rows and columns, where each column has a specific data type (e.g., VARCHAR2, NUMBER, DATE). The syntax allows you to specify column names, data types, constraints (e.g., PRIMARY KEY, NOT NULL), and storage options. Advanced features include partitioning, tablespaces, and external tables for efficient data organization.

In this tutorial, you will learn how to create a table in Oracle SQL with examples. We'll begin by exploring the basic syntax.

Oracle SQL Create Table Syntax

Here's the basic syntax for creating a table in Oracle SQL:

CREATE TABLE table_name (
    column1 datatype1 [constraint],
    column2 datatype2 [constraint],
    column3 datatype3 [constraint],
    ...
);

Oracle Create Table Examples

Below I am giving 17 different CREATE TABLE scenarios, from basic table creation to advanced features like partitioning and tablespaces. Each example includes a short but clear explanation and practical SQL code that you can use as a reference.

Creating a Table in Oracle without Constraints

This is the most basic form of table creation in Oracle. It allows you to define a simple table structure without any data integrity rules or restrictions. Useful for temporary tables or when data validation isn't required at the database level.

CREATE TABLE employees (
    emp_id NUMBER,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    hire_date DATE
);

Creating a Table with a Primary Key in Oracle

A primary key uniquely identifies each record in a table. It automatically creates a unique index and enforces the NOT NULL constraint. It is essential for maintaining data integrity and establishing relationships between tables.

CREATE TABLE employees (
    emp_id NUMBER,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    CONSTRAINT pk_employees PRIMARY KEY (emp_id)
);

Creating a Table with a Foreign Key in Oracle

Foreign keys establish relationships between tables by ensuring referential integrity. They prevent orphaned records by ensuring that child table values correspond to the parent table's existing values.

CREATE TABLE orders (
    order_id NUMBER,
    emp_id NUMBER,
    order_date DATE,
    CONSTRAINT fk_emp FOREIGN KEY (emp_id)
    REFERENCES employees(emp_id)
);

Creating a Table with Default Values in Oracle

Default values automatically populate columns when no specific value is provided during insertion. This is particularly useful for audit columns, status flags, or timestamps that should have predetermined values.

CREATE TABLE products (
    product_id NUMBER,
    product_name VARCHAR2(100),
    active VARCHAR2(1) DEFAULT 'Y',
    create_date DATE DEFAULT SYSDATE
);

Creating a Table with an Index in Oracle

Indexes improve query performance by providing faster data access paths. While they add overhead to DML operations, they significantly speed up data retrieval, especially for large tables.

CREATE TABLE customers (
    customer_id NUMBER,
    customer_name VARCHAR2(100)
);
CREATE INDEX idx_customer_name ON customers(customer_name);

Creating a Table with Check Constraint

Check constraints enforce business rules by validating data before it's inserted or updated. They ensure data consistency and prevent invalid values from being stored in the database.

CREATE TABLE employees (
    emp_id NUMBER,
    salary NUMBER,
    CONSTRAINT chk_salary CHECK (salary > 0 AND salary < 1000000)
);

Creating a Table with from Another Table

This method copies the structure and optionally the data from an existing table. Useful for creating backup tables, test environments, or when you need to replicate table structures with modifications.

CREATE TABLE employees_backup 
AS SELECT * FROM employees WHERE 1=2;

In the example above, only the table structure will be copied, not the data, because the condition 1=2 will never be true. To copy the data as well as the structure, omit the condition.

Creating a Table with Sequence in Oracle

Sequences generate unique numbers automatically, commonly used for primary key values. They provide a reliable way to generate unique identifiers without worrying about concurrent insertions.

CREATE SEQUENCE emp_seq START WITH 1 nocache order;
CREATE TABLE employees (
    emp_id NUMBER DEFAULT emp_seq.NEXTVAL,
    emp_name VARCHAR2(100)
);

Creating a Table with Partitioning in Oracle

Partitioning divides large tables into smaller, manageable pieces while maintaining a single logical view. This improves query performance, availability, and simplifies maintenance for very large tables.

CREATE TABLE sales (
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION sales_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

Creating a Table as Select in Oracle

Creates and populates a new table from a SELECT statement in one operation. Useful for creating summary tables, data marts, or materializing complex query results.

CREATE TABLE active_employees 
AS SELECT * FROM employees WHERE status = 'ACTIVE';

Creating a Table in Oracle with Unique Constraint

Unique constraints prevent duplicate values in specified columns. Unlike primary keys, they allow NULL values and you can have multiple unique constraints per table.

CREATE TABLE employees (
    emp_id NUMBER,
    email VARCHAR2(100),
    CONSTRAINT unq_email UNIQUE (email)
);

Creating a Table in Oracle with NOT NULL Constraint

NOT NULL constraints ensures that a column must always contain a value. Essential for columns where having no value would make the record meaningless or cause application errors.

CREATE TABLE employees (
    emp_id NUMBER NOT NULL,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL
);

Creating a Table in Oracle with BLOB Datatype

BLOB columns store large binary objects such as images, audio, or video files. They can store up to 4GB of binary data and are perfect for applications requiring unstructured binary storage.

CREATE TABLE documents (
    doc_id NUMBER,
    doc_name VARCHAR2(100),
    doc_content BLOB
);

Creating a Table in Oracle with CLOB datatype

CLOB columns store large text data such as documents or XML files. They can hold up to 4GB of character data and are ideal for storing formatted documents or any large text content.

CREATE TABLE articles (
    article_id NUMBER,
    title VARCHAR2(200),
    content CLOB
);

Creating a Table in Oracle with Auto-Increment

Auto-incrementing columns (identity columns in Oracle 12c+) automatically generate sequential values for each new row, simplifying unique identifier generation.

CREATE TABLE employees (
    emp_id NUMBER GENERATED ALWAYS AS IDENTITY,
    emp_name VARCHAR2(100)
);

Creating a Table in Specific Schema

Creating tables in specific schemas helps organize database objects and control access permissions. Essential for maintaining proper database organization in multi-user environments.

CREATE TABLE hr.employees (
    emp_id NUMBER,
    emp_name VARCHAR2(100)
);

Creating a Table in Oracle with Tablespace

Tablespaces are logical storage units for managing database files. Specifying a tablespace during table creation gives you control over where the table's data is physically stored.

CREATE TABLE employees (
    emp_id NUMBER,
    emp_name VARCHAR2(100)
) TABLESPACE users;

That's all for this post and I hope these CREATE TABLE examples will be helpful for any Oracle Database developers for their day-to-day work.

Also, try our Oracle SQL Script Browser tool. It's very helpful for Oracle developers to view and edit DDL SQL scripts.