Creating tables is the foundation of database development in Oracle. A table defines how your data will be structured, stored, and managed. In Oracle Database 23ai, the CREATE TABLE statement continues to be the central command, and while the basics remain the same, the release also introduces modern features like vector datatypes and blockchain tables. This tutorial walks through the complete process of creating tables, explains the syntax in detail, and provides working examples that you can run in your own environment.
Basic Syntax of CREATE TABLE
The general syntax for creating a table in Oracle 23ai is:
CREATE TABLE table_name (
column_name1 datatype [constraint],
column_name2 datatype [constraint],
...
[table_constraints]
);
- table_name: Name of the table.
- column_name: Name of the column.
- datatype: Defines the type of data (e.g.,
NUMBER,VARCHAR2,DATE,CLOB). - constraint: Rules such as
PRIMARY KEY,NOT NULL,UNIQUE, orCHECK. - table_constraints: Additional rules like foreign keys or composite primary keys.
Creating a Simple Table in Oracle Database 23ai
The simplest way to create a table is to define columns and their datatypes.
CREATE TABLE employees ( employee_id NUMBER(6), first_name VARCHAR2(20), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(50), hire_date DATE, salary NUMBER(8,2) );
This table defines six columns, with last_name required (NOT NULL). Oracle creates the table but does not allocate space until rows are inserted.
Adding Constraints
Constraints enforce rules on the data. These can be applied at the column level or as table-level constraints.
Primary Key Constraint
CREATE TABLE departments ( department_id NUMBER(4) PRIMARY KEY, department_name VARCHAR2(30) NOT NULL, location VARCHAR2(50) );
Here, department_id is the primary key and must be unique and not null.
Foreign Key Constraint
CREATE TABLE employees (
employee_id NUMBER(6) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(25) NOT NULL,
department_id NUMBER(4),
CONSTRAINT fk_dept FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);
This ensures every department_id in employees exists in departments.
Default and Check Constraints
CREATE TABLE jobs ( job_id NUMBER(4) PRIMARY KEY, job_title VARCHAR2(40) NOT NULL, min_salary NUMBER(6,2) DEFAULT 2000 CHECK (min_salary >= 1000), max_salary NUMBER(6,2) CHECK (max_salary <= 20000) );
DEFAULTprovides a value if none is supplied.CHECKvalidates that entered data matches a condition.
Using Identity Columns
Oracle 23ai supports identity columns to automatically generate unique numbers, reducing the need for explicit sequences.
CREATE TABLE customers ( customer_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, first_name VARCHAR2(30), last_name VARCHAR2(30), email VARCHAR2(50) UNIQUE );
The customer_id will auto-increment with each insert.
New Feature in Oracle 23ai: Vector Datatype
One of the major new features in Oracle 23ai is the VECTOR datatype, designed for AI and machine learning use cases such as semantic search or recommendation systems.
CREATE TABLE product_embeddings ( product_id NUMBER PRIMARY KEY, description VARCHAR2(200), embedding VECTOR(1536) -- store AI embeddings );
The VECTOR datatype allows you to store high-dimensional numeric arrays for similarity queries and AI-driven search.
Blockchain and Immutable Tables
Oracle 23ai supports special table types to ensure data immutability and tamper resistance. These are useful for auditing and compliance.
Immutable Table
CREATE IMMUTABLE TABLE audit_logs ( log_id NUMBER PRIMARY KEY, action VARCHAR2(100), user_name VARCHAR2(50), log_date TIMESTAMP DEFAULT SYSTIMESTAMP ) NO DROP UNTIL 7 DAYS IDLE NO DELETE UNTIL 30 DAYS AFTER INSERT;
Immutable tables prevent updates or deletes and enforce retention rules.
Blockchain Table
CREATE BLOCKCHAIN TABLE audit_chain (
log_id NUMBER PRIMARY KEY,
action VARCHAR2(100),
user_name VARCHAR2(50),
log_date TIMESTAMP DEFAULT SYSTIMESTAMP
)
NO DROP UNTIL 1 DAYS IDLE
NO DELETE UNTIL 16 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1";
Blockchain tables chain each row with cryptographic hashes, making tampering detectable.
Partitioned Tables
Partitioning helps manage large tables efficiently by splitting data into smaller, more manageable pieces.
CREATE TABLE sales_partitioned (
sale_id NUMBER,
region VARCHAR2(20),
sale_date DATE,
amount NUMBER(10,2),
CONSTRAINT pk_sales PRIMARY KEY (sale_id)
)
PARTITION BY RANGE (sale_date) (
PARTITION q1_2025 VALUES LESS THAN (TO_DATE('2025-04-01','YYYY-MM-DD')),
PARTITION q2_2025 VALUES LESS THAN (TO_DATE('2025-07-01','YYYY-MM-DD')),
PARTITION future VALUES LESS THAN (MAXVALUE)
);
Each partition stores rows for a different quarter, improving query performance and maintenance.
Practical Example: End-to-End Table Creation
The following script creates a working schema that demonstrates constraints, identity columns, vector datatype, and partitions.
-- Departments table
CREATE TABLE departments (
department_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
department_name VARCHAR2(50) NOT NULL,
location VARCHAR2(50)
);
-- Employees table
CREATE TABLE employees (
employee_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
first_name VARCHAR2(30),
last_name VARCHAR2(30) NOT NULL,
email VARCHAR2(50) UNIQUE,
department_id NUMBER,
hire_date DATE DEFAULT SYSDATE,
salary NUMBER(8,2) CHECK (salary > 0),
CONSTRAINT fk_emp_dept FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);
-- Products with vector embeddings
CREATE TABLE products (
product_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_name VARCHAR2(100) NOT NULL,
description VARCHAR2(200),
embedding VECTOR(512) -- AI feature
);
-- Orders partitioned by date
CREATE TABLE orders (
order_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id NUMBER NOT NULL,
order_date DATE DEFAULT SYSDATE,
order_total NUMBER(10,2) CHECK (order_total > 0)
)
PARTITION BY RANGE (order_date) (
PARTITION q1_2025 VALUES LESS THAN (TO_DATE('2025-04-01','YYYY-MM-DD')),
PARTITION q2_2025 VALUES LESS THAN (TO_DATE('2025-07-01','YYYY-MM-DD')),
PARTITION future VALUES LESS THAN (MAXVALUE)
);
This script is fully executable in Oracle 23ai on any environment and demonstrates real-world practices.
See also: How to Insert Data into a Table in Oracle 23ai
Conclusion
Creating tables in Oracle 23ai is a straightforward process, but the database offers many advanced features to improve efficiency, reliability, and adaptability to modern workloads. The CREATE TABLE syntax allows you to define columns, constraints, and storage options.
With Oracle 23ai, you also gain access to powerful new features such as vector datatypes for AI-driven workloads and immutable or blockchain tables for compliance. By combining these options with best practices like primary keys, defaults, and partitioning, you can design a robust and scalable database structure that meets both traditional and modern application requirements.
