How to create a table in an Oracle database

Updated: 11/13/2018 by Computer Hope
Oracle logo

An Oracle database can have tens or even hundreds of tables in it, each with hundreds or thousands of rows of data. To store additional, custom data in an Oracle database, it is often best to create a new table for the data to be added. You can also define the columns of data to be stored in the new table.

Creating a basic table

You need to use the Oracle SQL (Structured Query Language) programming code to create a table. When defining a new table, you must include the name of the table, and you must also define at least one column to be included in the table. An example SQL statement to create a table is shown below.

CREATE TABLE mytable
(
column1 VARCHAR(30),
column2 DATE
);

The CREATE TABLE line tells the database to create a new table, named as "mytable". The table is defined as having two columns in it. After the CREATE TABLE line, the columns in the table are defined, with parentheses enclosing the column definition code.

The first defined column, named "column1", is defined as a VARCHAR(30) type of column, meaning it can store alphanumeric data up to 30 characters to be stored in that column per row of data. The second defined column, named "column2", is defined as a DATE type of column, meaning it can store date values.

Data types for columns

Each column in a table has a data type defined for it. The most common data types used in a database table include the following.

  • BOOLEAN - allows for storing values of TRUE or FALSE
  • DATE - allows for storing date values
  • INT - allows for storing of integer values, no decimals
  • TIME - allows for storing of time values (hours, minutes, seconds)
  • TIMESTAMP - allows for storing of date and time values
  • VARCHAR(n) - allows for storing of alphanumeric values, with a maximum character length defined as n

Defining column as NOT NULL

A column can also be defined as being NOT NULL, meaning it cannot be blank and must always have a value. Below is an example of creating a table with a column defined as NOT NULL.

CREATE TABLE mytable
(
column1 VARCHAR(30),
column2 DATE
column3 VARCHAR(15) NOT NULL
);

The above example is defining "column3" as being NOT NULL, requiring a value be entered in it for each row of data.

Primary key column in a table

A table in an Oracle database can hold many columns of data. Each table generally has one column defined as a PRIMARY KEY, which stores unique values that identify each row of data. The primary key also allows a table to link to another table, so that data in one table can match up with data in another table.

A primary key is often a numeric value, but it could be alphanumeric as well. The example SQL statement below creates a table with a column designated as the primary key.

CREATE TABLE mytable
(
pk_ID INT PRIMARY KEY,
column1 VARCHAR(30),
column2 DATE,
column3 VARCHAR(15) NOT NULL
);

The table created with the above example SQL statement includes a column named "pk_ID", defined as the PRIMARY KEY and as an Integer data type, meaning it can only contain numeric values. By default, being the Primary Key column, it also cannot be blank, or NULL.