Oracle CREATE TABLE Command in PL/SQL with 10 Examples

In this article, we are going to talk about the CREATE TABLE command. To be more precise, we will focus on how to create a table in Oracle with a primary and foreign key, as well as not null and date columns, take a close look at how to create a new table on a basis of the existing one, and more. Also, we will review the perks and benefits of using dbForge Studio for Oracle when working with tables.

Contents

• Oracle CREATE TABLE statement syntax
  • CREATE TABLE example
• CREATE TABLE with PRIMARY KEY constraint
• CREATE TABLE with FOREIGN KEY
• CREATE TABLE with NOT NULL column
• CREATE TABLE with a date column
• Oracle alternative to SQL CREATE TABLE IF NOT EXISTS
• Create a new table from another table using CREATE TABLE AS SELECT
• CREATE TABLE from SELECT in PL/SQL
• How to create and insert data into a temporary table
• CREATE TABLE in a wink using dbForge for Oracle
• Conclusion

Creating tables in Oracle is one of the ways to arrange data, so it’s critical to learn as much as possible about the CREATE TABLE command. There are several different ways to create a table in Oracle and we’ll cover them further in this guide. 

Please note that you must have the CREATE TABLE system privilege to create a new table. If you wish to create a table in another user’s schema, you must have the CREATE ANY TABLE system privilege. In case you are the owner of the table, you must have the UNLIMITED TABLESPACE system privilege or the quota for the tablespace that the table contains.

Additionally, you must have the EXECUTE object privilege or the EXECUTE ANY TYPE system privilege to create an object table or a relational table that contains an object type column if you want to have access to all the types that are referenced by the table.

The examples in this article relate to Oracle 19c version, but the methods are the same for all Oracle versions in use (including Oracle 10g, 11g, 12c, etc.).

Oracle CREATE TABLE statement syntax

Let us begin with the basics. To create a new table in an Oracle database, the CREATE TABLE statement can be used. The CREATE TABLE syntax in Oracle is as follows:

CREATE TABLE schema_name.table_name (
    column_1 data_type column_constraint,
    column_2 data_type column_constraint,
    ...
    table_constraint
 );

Let us take a closer look at the provided syntax:

1. schema_name.table_name: names of the table and schema the new table belongs to.
2. column_1 and column_2 data_type column_constraints: placeholders for the column names.
3. data_type: NUMBER, VARCHAR, etc.
4. column_constraint: NOT NULL, primary key, check, etc.
5. table_constraint: table constraints (primary key, foreign key, check).

You will find the Oracle CREATE TABLE syntax example further in this article.

Note:
In addition to learning how to create a table in Oracle, you might also wish to deepen your knowledge about the Oracle ALTER TABLE statement.

CREATE TABLE example

It is always better to learn from practice. Therefore, let’s look at the Oracle CREATE TABLE example:

The following example shows how to create a three-column table named employees:

CREATE TABLE employees
( employee_id number(10) NOT NULL,
  employee_name varchar2(50) NOT NULL,
  city varchar2(50)
);

• Column 1 is named employee_id and has a number datatype (maximum 10 digits in length). It cannot contain null values.
• Column 2 is named customer_name and has a varchar2 datatype (50 maximum characters in length) and can not contain null values as well.
• Column 3 is named city and has a varchar2 datatype. Unlike the previous two, this column can contain null values.

CREATE TABLE with PRIMARY KEY constraint

Now, let’s see how to create a table in Oracle with a primary key. To define a primary key for the table, you can use our previously created table Employees, edit the Oracle SQL CREATE TABLE statement, and define the employee_id as the primary key:

CREATE TABLE employees
( employee_id number(10) NOT NULL,
  employee_name varchar2(50) NOT NULL,
  city varchar2(50),
  CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

The PRIMARY KEY clause allows specifying a column as the primary key one. You can use the primary key syntax to identify each unique row in the table. In contrast to other databases, Oracle enables a table to include only one primary key, and each field of the primary key must contain values other than NULL in order for the table to be considered valid. In the example above, we define the employee_id column as the Primary Key Column.

CREATE TABLE with FOREIGN KEY

Let’s take look at the syntax for the Oracle CREATE TABLE statement FOREIGN key. It can be both defined at a column level or table level:

CREATE TABLE table_name
(
col1 datatype [ NULL | NOT NULL ],
col2 datatype [ NULL | NOT NULL ],
...
col_n datatype [ NULL | NOT NULL ]
constraint <name> FOREIGN KEY (col1,col2) REFERENCES table(col1,col2)
)  tablespace <tablepace name>

CREATE TABLE table_name
(
col1 datatype [ NULL | NOT NULL ] constraint <name> primary key
,
col2 datatype [ NULL | NOT NULL ],
...
col_n datatype [ NULL | NOT NULL ]
)  tablespace <tablepace name>;

CREATE TABLE dept
( dept_id number(10) NOT NULL,
dept_name varchar2(50) NOT NULL,
CONSTRAINT dept_pk PRIMARY KEY (dept_id)
);

CREATE TABLE emp
( emp_no number(10) NOT NULL,
emp_name varchar2(50) NOT NULL,
dept_id number(10),
salary number(6),
CONSTRAINT emp_pk PRIMARY KEY (emp_no),
CONSTRAINT dept_fk
FOREIGN KEY (dept_id)
REFERENCES dept(dept_id) );

In this syntax:

• Column 1 is labeled emp_no, and it is formatted as a number, which means that it cannot include any null values.
• Column 2 is titled emp_name, which is built as varchar2(50) and cannot include any null values.
• Column 3 is named dept_id and has a number datatype.
• Column 4 is called salary and is also formatted as a number column.
• Table level primary key constraint emp_pk is defined on the key (emp_no).
• Table level foreign key constraints dept_fk  which references dept table dept_id.

CREATE TABLE with NOT NULL column

If you would like to specify that the column cannot be empty and must contain some value, you can define it as NOT NULL. Find the syntax for the CREATE TABLE command with the NOT NULL column in Oracle below:

CREATE TABLE employees_bdays
(
emp_name VARCHAR(30),
bday DATE
emp_id VARCHAR(15) NOT NULL
);

As you can see, emp_id implies entering a value for each row of data as it is NOT NULL.

CREATE TABLE with a date column

If you need to create a table with the date column in Oracle, the following syntax might come in handy:

CREATE TABLE employees_bdays (
bday DATE
);

To efficiently insert a date into the column, you need the to_date function which accepts a character string containing the date as well as another character string instructing it on how to interpret the date received.

to_date('01 December 2021','DD Month YYYY')
to_date('01/12/2021','DD/MM/YYYY')

Oracle alternative to SQL CREATE TABLE IF NOT EXISTS

Those users, who are used to a very helpful SQL CREATE TABLE IF NOT EXISTS command, might be disappointed as there is no such statement in Oracle. But if you still need to determine whether a table already exists before creating it in Oracle, you will find alternative solutions to this problem below.

If you receive an error (ORA-00955: name is already in use by an existing object) while trying to create a table, it can serve as an indicator that such a table is already there.

Create a new table from another table using CREATE TABLE AS SELECT

Sometimes, you need to copy table data and insert it into another one. It is quite easy to create a table like another table in Oracle, plus it’s very helpful. You save your time and effort if you want to create a table with the same structure. It also simplifies the process of testing.

To see how to create a table from another table in Oracle, look at the below script:

CREATE TABLE table_name AS (
SELECT select_query
);

It’s also referred to as CREATE TABLE AS SELECT (CTAS).

You can either enter the table_name for your new table or use the SELECT query to copy it. Also, you can enter SELECT * FROM old_table if you need to copy all the data to the new table. If you need to restrict the values to be copied across, the WHERE clause will be helpful for you.

CREATE TABLE from SELECT in PL/SQL

In Oracle, you can also create one table from another by entering the SELECT statement at the end of the CREATE TABLE statement. In this case, all the records from the old table will be copied to the new one. The syntax of the Oracle CREATE TABLE from the SELECT is as follows:

CREATE TABLE new_table
  AS (SELECT * FROM old_table);

As you can see, PL/SQL table creation from the SELECT command is very helpful. Note that you can also create a temporary table from SELECT.

How to create and insert data into a temporary table

Temporary tables are used in Oracle to store the data that belongs to one session or one transaction.

ORACLE temporary table is a DDL object with all the restrictions.

So how to create a temporary table in Oracle? You can use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table. To define if the data in the table is transaction-specific (the default) or session-specific, use the ON COMMIT clause.

The syntax for the transaction-specific data is as follows:

CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         operation CHAR(20))
      ON COMMIT DELETE ROWS;

CREATE TABLE in a wink using dbForge for Oracle

Time has always been one of the most valuable resources out there. Therefore, you might find using an appropriate IDE helpful in terms of saving your time. One of the best solutions is dbForge Studio for Oracle. It is a universal tool that can be of use for developing, administrating, and managing Oracle databases. This efficient GUI client is great for DBAs, database developers, software engineers, and analysts. dbForge Studio for Oracle allows for data synchronization between several Oracle servers and facilitates database development processes automation.

On opening the IDE for the first time, you will see the Database Connection Properties window. To open it manually, choose Database and click New Connection.

After that, fill in the corresponding fields and hit Test Connection.

If everything is configured correctly, you will see the Successfully connected message that will look somehow like this:

Having connected to your Oracle server, choose the user you wish to create a new table for. Our recent blog post on how to create a new user in Oracle might come in handy at this step.

The first step towards creating a new table is making a right-click on the required schema. Point to New Object and click the first option: Table.

1. In the Name text box of the Table Editor, enter a table name. In the example below, we are creating a table titled employees.

2. In the grid below, type in the names for the future columns, choose the data type, and whether they should be NOT NULL.

3. The Column properties will be displayed on the right-hand side of Table Editor.

4. You will see that all the actions you perform in Table Editor, are reflected in the SQL query at the bottom of the window.

Once the properties of the table are configured, click Apply Changes and that’s it!

Conclusion

Creating tables is one of the most common tasks when working with Oracle databases as it helps organize data. In our guide, we have offered a detailed walkthrough of how to create a table in Oracle using 10 different ways. You can use the Oracle SQL CREATE TABLE statement and execute the query manually or use an appropriate IDE to automate the process. In this case, dbForge Studio for Oracle is the best choice. Also, the tool can greatly increase your productivity because it has everything required for Oracle PL/SQL performance tuning.

Useful links

• Create a Database in Oracle
• Rename the table in Oracle
• PL/SQL Formatter