PL/SQL CREATE TABLE

The CREATE TABLE statement in Oracle PL/SQL is a powerful and essential command used to define and create a new table in a relational database. Tables are fundamental database objects that store data in a structured format, allowing for efficient data retrieval and manipulation. The CREATE TABLE statement enables you to specify the table’s structure, including column names, data types, constraints, and other attributes.

Syntax

Here is a basic syntax for the CREATE TABLE statement in Oracle PL/SQL:

CREATE TABLE table_name
(
   column1 datatype [ CONSTRAINT constraint_name ],
   column2 datatype [ CONSTRAINT constraint_name ],
   ...
   columnN datatype [ CONSTRAINT constraint_name ]
);

Let’s break down the components of this statement:

CREATE TABLE: This is the keywords that initiate the creation of a new table.

table_name: This is the name you choose for your table. It should be unique within the schema.

(column1, column2, …, columnN): These are the columns that you define for your table. Each column is specified with a name and a data type. The data type defines the kind of data that can be stored in that column (e.g., VARCHAR2, NUMBER, DATE).

CONSTRAINT constraint_name: This is an optional part of the statement where you can define constraints on the columns. Constraints enforce rules on the data stored in the table, such as ensuring uniqueness or defining a primary key.

Example

Here’s a more concrete example of a CREATE TABLE statement:

CREATE TABLE employees
(
   employee_id NUMBER(5) CONSTRAINT pk_employee_id PRIMARY KEY,
   first_name VARCHAR2(50),
   last_name VARCHAR2(50) NOT NULL,
   hire_date DATE,
   salary NUMBER(10,2),
   CONSTRAINT chk_salary CHECK (salary >= 0)
);

In this example, we’re creating a table named “employees” with columns such as “employee_id,” “first_name,” “last_name,” “hire_date,” and “salary.” The PRIMARY KEY constraint is applied to the “employee_id” column, ensuring its uniqueness. The “last_name” column is marked as NOT NULL to enforce that it must have a value. Additionally, a CHECK constraint is applied to the “salary” column to ensure that the salary is not negative.

The CREATE TABLE statement in Oracle PL/SQL allows for a wide range of options and flexibility in defining tables, making it a critical tool for database design and management.