Oracle CREATE TABLE OPERATOR

Oracle/PLSQL CREATE TABLE operator allows you to create and define a table.

CREATE TABLE syntax in Oracle/PLSQL

CREATE TABLE table_name_id
(
column1_id datatype [ NULL | NOT NULL ],
column2_id datatype [ NULL | NOT NULL ],
…
column_n_id datatype [ NULL | NOT NULL ]
);

Parameters or arguments

• table_name_id – The name of the table you want to create.
• column1_id, column2_id, … column_n_id – The columns you create in the table. Each column must have a data type. The column must be defined either as “NULL” or “NOT NULL”, if this value is not specified, then the default is “NULL”.

Let’s consider an example how to use TABLE in Oracle/PLSQL CREATE.

CREATE TABLE
( custom_id number(10) NOT NULL,
custom_name varchar2(50) NOT NULL,
city_id varchar2(50)
);

In this example, CREATE TABLE creates a customer table that has 3 columns:

1. The first custom_id column, which is created with numeric data type (maximum 10 digits in length) and cannot take NULL value.
2. The second column custom_name with data type varchar2 (max 50 characters), also can not take value NULL.
3. The third column city_id, which has the data type varchar2, it can take value NULL.

Now the only problem in Oracle/PLSQL CREATE TABLE is that no primary key is defined for the customs table. We will change the content of the CREATE TABLE offer and define custom_id as the primary key as follows:

CREATE TABLE customs_id
( custom_id number(10) NOT NULL,
custom_name varchar2(50) NOT NULL,
city_id varchar2(50),
CONSTRAINT customs_pk PRIMARY KEY (custom_id)
);

Practical Exercise #1

Create an Oracle/PLSQL table called suppls, which stores information about custom_id, custom_name, and city_id.

Solution for Practical Exercise #1:

CREATE TABLE suppls
( suppl_id number(10) NOT NULL,
suppl_name varchar2(50) NOT NULL,
address_id varchar2(50),
city_id varchar2(50),
state_id varchar2(25),
zip_code_id varchar2(10)
);

Practical Exercise #2

Create an Oracle/PLSQL table with the name of customers that stores information about suppl_id, name and address_id.

But this time, custom_id must be the primary key for the table.

Solution for Practical Exercise #2:

CREATE TABLE customs
( custom_id number(10) NOT NULL,
custom_name varchar2(50) NOT NULL,
address_id varchar2(50),
city_id varchar2(50),
state_id varchar2(25),
zip_code_id varchar2(10),
CONSTRAINT customers_pk PRIMARY KEY (custom_id)
);

Practical Exercise #3

Based on the departs table below, create a table in Oracle/PLSQL named empls (employees) that stores information about empl_number (employee number), empl_name (employee name), depart (department), and salary_id (salary). Primary key for the employee table must be empl_number. Creation of a foreign key in the empls table, which refers to the departs table, based on the depart_id field.

Solution for Practical Exercise #3:

CREATE TABLE employees
( empl_number number(10) NOT NULL,
empl_name varchar2(50) NOT NULL,
depart_id number(10),
salary_id number(6),
CONSTRAINT empl_pk PRIMARY KEY (empl_number),
CONSTRAINT fk_departs
FOREIGN KEY (depart_id)
REFERENCES departs(depart_id)
);

Oracle SQL Tutorial – CREATE TABLE