Creating Tables in Oracle SQL

Key Concepts

Creating tables in Oracle SQL involves defining the structure of the table, including column names, data types, and constraints. Understanding the following key concepts is essential for effectively creating tables:

1. CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in the database. It specifies the table name and the columns with their respective data types and constraints.

2. Column Definitions

Each column in a table must be defined with a name and a data type. The data type determines the kind of data that can be stored in the column, such as numbers, text, dates, etc.

3. Primary Key

A primary key is a column or a set of columns that uniquely identifies each row in a table. It ensures that each row in the table is unique and provides a way to reference specific rows.

4. Foreign Key

A foreign key is a column or a set of columns in one table that refers to the primary key in another table. It establishes a relationship between the two tables, ensuring data integrity.

5. Constraints

Constraints are rules that enforce data integrity. Common constraints include NOT NULL, UNIQUE, CHECK, and DEFAULT.

Detailed Explanation

1. CREATE TABLE Statement

The basic syntax for the CREATE TABLE statement is:


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

Example: Creating a table named "Employees" with columns for EmployeeID, FirstName, LastName, and HireDate:


CREATE TABLE Employees (
    EmployeeID NUMBER PRIMARY KEY,
    FirstName VARCHAR2(50) NOT NULL,
    LastName VARCHAR2(50) NOT NULL,
    HireDate DATE
);

2. Column Definitions

Each column in the table must be defined with a name and a data type. Common data types include NUMBER, VARCHAR2, DATE, and CHAR.

Example: Defining columns with different data types:


CREATE TABLE Products (
    ProductID NUMBER,
    ProductName VARCHAR2(100),
    Price NUMBER(10, 2),
    ReleaseDate DATE
);

3. Primary Key

A primary key uniquely identifies each row in a table. It is defined using the PRIMARY KEY constraint.

Example: Defining a primary key for the "Employees" table:


CREATE TABLE Employees (
    EmployeeID NUMBER PRIMARY KEY,
    FirstName VARCHAR2(50),
    LastName VARCHAR2(50)
);

4. Foreign Key

A foreign key establishes a relationship between two tables. It is defined using the FOREIGN KEY constraint.

Example: Creating a "Orders" table with a foreign key referencing the "Employees" table:


CREATE TABLE Orders (
    OrderID NUMBER PRIMARY KEY,
    EmployeeID NUMBER,
    OrderDate DATE,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

5. Constraints

Constraints enforce data integrity. Common constraints include NOT NULL, UNIQUE, CHECK, and DEFAULT.

Example: Adding constraints to the "Products" table:


CREATE TABLE Products (
    ProductID NUMBER PRIMARY KEY,
    ProductName VARCHAR2(100) NOT NULL,
    Price NUMBER(10, 2) CHECK (Price > 0),
    ReleaseDate DATE DEFAULT SYSDATE
);

Understanding and mastering these concepts is essential for creating well-structured and efficient tables in Oracle SQL. By defining columns, primary keys, foreign keys, and constraints, you can ensure data integrity and optimize your database design.