How to Create and Manage Tables in Oracle Database

March 20, 2025

Introduction

Tables are the fundamental building blocks of any relational database, including Oracle Database. They store data in a structured format using rows and columns. In this blog, we will walk you through the process of creating, modifying, and managing tables in Oracle Database.

Creating a Table in Oracle Database

To create a table in Oracle, use the CREATE TABLE statement. You must define the table name, column names, data types, and constraints.

Basic Syntax:

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

Example:

Let's create a table called EMPLOYEES with columns EMP_ID, NAME, AGE, SALARY, and DEPARTMENT.

CREATE TABLE EMPLOYEES (
    EMP_ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    AGE NUMBER(3) CHECK (AGE > 18),
    SALARY NUMBER(10,2),
    DEPARTMENT VARCHAR2(50)
);

Explanation:

EMP_ID is a primary key, ensuring uniqueness.
NAME is required (NOT NULL).
AGE must be greater than 18 (CHECK constraint).
SALARY holds numeric values with two decimal places.
DEPARTMENT stores the department name.

Altering a Table

Sometimes, after creating a table, you may need to modify its structure. The ALTER TABLE statement allows you to add, modify, or drop columns.

Adding a Column:

ALTER TABLE EMPLOYEES ADD (EMAIL VARCHAR2(100));

Modifying a Column:

ALTER TABLE EMPLOYEES MODIFY (SALARY NUMBER(12,2));

Dropping a Column:

ALTER TABLE EMPLOYEES DROP COLUMN AGE;

Deleting a Table

If a table is no longer needed, you can delete it using the DROP TABLE statement.

DROP TABLE EMPLOYEES;

⚠ Caution: This permanently removes the table and all its data.

Alternatively, if you want to keep the table structure but remove all rows, use:

TRUNCATE TABLE EMPLOYEES;

This is faster than DELETE as it does not generate undo logs.

Managing Tables with Constraints

Constraints ensure data integrity in tables. Here are some common constraints:

PRIMARY KEY – Ensures unique values for a column.
FOREIGN KEY – Establishes a relationship between two tables.
NOT NULL – Prevents NULL values.
CHECK – Enforces specific conditions.
UNIQUE – Ensures column values are unique.

Example of adding a foreign key:

CREATE TABLE DEPARTMENTS (
    DEPT_ID NUMBER PRIMARY KEY,
    DEPT_NAME VARCHAR2(50)
);

ALTER TABLE EMPLOYEES ADD CONSTRAINT fk_dept FOREIGN KEY (DEPARTMENT) REFERENCES DEPARTMENTS(DEPT_NAME);

Viewing Table Information

To check the structure of a table, use:

DESC EMPLOYEES;

To list all tables in your schema:

SELECT TABLE_NAME FROM USER_TABLES;

Conclusion

Managing tables in Oracle Database is a critical skill for database administrators and developers. By understanding how to create, modify, and manage tables effectively, you can ensure a well-structured and optimized database.

Are you looking for more in-depth Oracle tutorials? Let us know in the comments! 🚀

Search This Blog

Oracle