Oracle CREATE TABLE Statement with Examples

Oracle CREATE Table Statement is used for creating new tables in the database.

Syntax

CREATE TABLE schema_name.table_name (
    column1 data_type column_constraint,
    column2 data_type column_constraint,
    ...
    table_constraint
 );

Here,

schema_name : It is the name of schema in Oracle database under which we want to create our table.

table_name : It is the name of table that we want to give to new table.

column1, column2 : These are the names of columns within the table.

data_type : It is the data type of the column in table.

column_constraint : It is the constraint that we give to the column like NOT NULL, PRIMARY KEY etc.

table_constraint : We can give table level constraints at the end.

Note:

A user needs to have CREATE table privileges before creating a new table in its schema. If a user intends to create a new table in any other schema then it needs to have CREATE ANY TABLE system privileges. Additionally, one must have quota for tablespace or UNLIMITED TABLESPACE system privilege.

Some naming rules that you need to keep in mind while naming Tables and Columns:

• Names must begin with an alphabet
• Names can be between 1-30 characters long
• Only A-Z, a-z, 0-9, _, $, and # are allowed
• Duplicate names are not allowed
• Names should not be a reserved Oracle keyword.

1. Oracle CREATE TABLE Statement Example

We use the following query for creating Founders table in HR schema:

CREATE TABLE hr.founders(
    founder_id NUMBER,
    first_name VARCHAR2(100) NOT NULL,
    last_name VARCHAR2(100) NOT NULL,
    company_name VARCHAR2(100) NOT NULL,
    PRIMARY KEY(founder_id)
);

In this example, the Founders table has 4 columns which are: founder_id, first_name, last_name and company_name.

The founder_id has a NUMBER data type and has a PRIMARY KEY constraint, that we give at the end. By definition, we can only enter NON- NULL and unique rows in this column.

The first_name, last_name and company_name are columns having VARCHAR2 data type and a max length of 100 characters. Having a NOT NULL constraint prevents user from inserting NULL values in this column.

2. CREATE TABLE using subquery

We can create a new table using the structure and data of existing table using a subquery. Let’s look at one example:

CREATE TABLE HIGH_INCOME_EMPLOYEES
AS
SELECT employee_id, first_name, last_name, salary
FROM EMPLOYEES
WHERE salary > 10000 ;

Result:

The query returns a new table named ‘High_income_employees’ where salary of each employee is greater than 10000.

In this article, we learned how to use CREATE TABLE statement for creating a new new table in Oracle database.