Creating Tables in Oracle

Introduction

Oracle stores information in tables. Tables consists of columns and rows. Each column head has a name. It can be EMPLOYEE NUMBER, NAME, DESIGNATION etc. Another table can have CUSTOMER NUMBER, CUSTOMER NAME, CUSTOMER ADDRESS, ORDER NO, ORDER DATE etc. Each row can have unique set of records.

Heap tables

When we refer to tables we refer to heap tables. They are simple tables without constraints. We will learn about constratints later. A heap table is created as follows:

CREATE TABLE MYTABLE (
  A number,
  B varchar2(10)
);

Supposing you wish to create a table to store Employee Information; you can create a table as follows:

create table EMPLOYEE (
  EMPLOYEE_NO number,
  EMPLOYEE_NAME varchar2(40),
  DESIGNATION varchar2(40),
  PLACE_OF_WORK varchar2(40), 
  DATE_OF_BIRTH date
);

Supposing you wish to drop a table you can do so by using the following code:

drop table EMPLOYEE;

Primary Key

In database Management the Primary Key plays a very important role. The purpose of the Primary Key is to uniquely identify a record set of a table. The Primary Key cannot hold a null value. Similarly the primary key cannot hold duplicate values. For example the EMPLOYEE NO of the EMPLOYEE table can be considered as a Ptimary Key.

You can create a table including a Primary Key as follows:

create table ORDERS (
  order_id     number primary key,
  order_date   date,
  customer_id  number
);

Supposing you wish to create a table to store Employee Information; you can create a table as follows:

create table EMPLOYEE (
  EMPLOYEE_NO number,
  EMPLOYEE_NAME varchar2(40),
  DESIGNATION varchar2(40),
  PLACE_OF_WORK varchar2(40), 
  DATE_OF_BIRTH date
);

Assuming that we wish to create a table to store customer information such as Customer ID, Customer Name, Customer Address etc. We can use the following code:

create table CUSTOMERS (
  CUSTOMER_ID     number primary key
  CUSTOMER_NAME   VARCHAR2(40),
  CUSTMER_ADDRESS VARCHAR2(40)  
);

Now we can create the constraints together with the create statement. As a foreign key references. Use the following code to create the ORDERS table defining the Foreign Key (In this Example CUSTOMER_ID)

create table ORDERS (
  ORDER_ID     number primary key
  ORDER_DATE   date,
  CUSTOMER_ID  references customers
)