How to Create a Table in Oracle

July 29, 2024 By Matthew Rathbone

The CREATE TABLE statement in Oracle is used to define a new table, specifying its columns and their data types. Here’s the basic syntax structure:

CREATE TABLE table_name (
    column1 data_type constraint,
    column2 data_type constraint,
    ...
);

Basic Example

Let’s create a table named employees with the following columns: employee_id, first_name, last_name, email, and hire_date.

CREATE TABLE employees (
    employee_id NUMBER(10) PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    email VARCHAR2(100) UNIQUE,
    hire_date DATE DEFAULT SYSDATE
);

Expected Output:

Table EMPLOYEES created.

Column Definitions and Data Types

Each column in a table must have a name and a data type. Oracle supports various data types, including:

NUMBER: Numeric values. Can specify precision and scale.
VARCHAR2: Variable-length character strings.
DATE: Date and time values.
BLOB: Binary Large Object, used for storing binary data.

Example: Specifying Data Types

CREATE TABLE products (
    product_id NUMBER(10) PRIMARY KEY,
    product_name VARCHAR2(255) NOT NULL,
    price NUMBER(10, 2),
    created_at DATE DEFAULT SYSDATE
);

Expected Output:

Table PRODUCTS created.

Adding Constraints

Constraints are rules applied to columns to ensure data integrity. Common constraints include:

PRIMARY KEY: Uniquely identifies each row.
UNIQUE: Ensures all values in a column are unique.
NOT NULL: Ensures that a column cannot have NULL values.
CHECK: Ensures that all values in a column satisfy a specific condition.
FOREIGN KEY: Establishes a link between columns in two tables.

Example: Adding Constraints:

CREATE TABLE orders (
    order_id NUMBER(10) PRIMARY KEY,
    customer_id NUMBER(10) REFERENCES customers(customer_id),
    order_date DATE NOT NULL,
    status VARCHAR2(20) CHECK (status IN ('Pending', 'Shipped', 'Delivered'))
);

Expected Output:

Table ORDERS created.

Table Creation with Beekeeper Studio

You can quickly create a new table in Beekeeper Studio by navigating to the schema browser sidebar and pressing the ‘+’ button. It also provides ‘structure view’ for tables where you can add, remove, rename columns, and other database functions. Beekeeper Studio is an open-source SQL editor and database manager which supports Oracle and many other databases. It provides a user-friendly, intuitive interface for creating, managing, and querying your tables.

Advanced Table Options and Considerations

When creating a table, consider the following options:

Tablespaces

A tablespace in Oracle is a logical storage unit that groups related data files together. It acts as a container for database objects, like tables and indexes. Tablespaces provide a way to allocate and manage storage in a more organized manner.

CREATE TABLE sales (
    sale_id NUMBER(10) PRIMARY KEY,
    product_id NUMBER(10) NOT NULL,
    sale_date DATE
) TABLESPACE sales_data;

Storage Parameters

Storage parameters define how Oracle allocates space for tables and indexes within a tablespace. These parameters help in managing the physical storage and controlling how data is stored and accessed.

CREATE TABLE logs (
    log_id NUMBER(10) PRIMARY KEY,
    log_message CLOB
) STORAGE (INITIAL 10M NEXT 5M);

Why Use Tablespaces and Storage Parameters?

Organizational Benefits: Tablespaces help you group related data together, making it easier to manage and back up.
Performance Optimization: By placing frequently accessed tables and indexes on faster disks or separating them from less frequently accessed data, you can optimize performance.
Data Management: Storage parameters help in efficient space allocation and management, reducing the likelihood of fragmentation and improving overall database performance.

Conclusion

Creating tables in Oracle involves understanding data types, constraints, and storage options. Following the examples and guidelines provided, should help you more effectively manage your data. Remember to consider data integrity and performance when defining your table structures.

Other articles you may enjoy: