Oracle - SQL Table Basics

Create Table

The mechanics of creating a table are relatively straight forward. Being able to design a well thought out database that will scale to meet the needs of a large scale enterprise is a very challenging undertaking. In these examples we will be working through development of a fairly simple three table database. In doing so we will get to cover a good deal of the development basics for creating tables and indexes.

Creating the person table

CREATE TABLE PERSON
     (
        NUM             NUMBER          NOT NULL , 
        FIRSTNAME       VARCHAR2(20)        NULL , 
        GENDER_CODE     CHAR(1)             NULL , 
        BIRTH_DTTM      DATE                NULL , 
        INACTIVE_DATE   DATE                NULL , 
        LASTNAME        VARCHAR2(30)        NULL , 
        CONSTRAINT PK_PERSON PRIMARY KEY (NUM)
     )

NOCACHE
LOGGING
/

Creating the phone table

CREATE TABLE PHONE
     ( 
        PERSON_NUM   NUMBER       NOT NULL  , 
        TYPE_CODE    CHAR(3)      NOT NULL  , 
        AREA_CODE    CHAR(3)          NULL  , 
        EXCHANGE     CHAR(3)          NULL  , 
        EXTENSION    CHAR(4)          NULL  , 
        CONSTRAINT PK_PHONE PRIMARY KEY (PERSON_NUM, TYPE_CODE)
     )

NOCACHE
LOGGING
/

Creating the address table

CREATE TABLE ADDRESS
     ( 
        PERSON_NUM    NUMBER       NOT NULL  , 
        TYPE_CODE     CHAR(4)      NOT NULL  , 
        STREET1       CHAR(30)         NULL  , 
        STREET2       CHAR(30)         NULL  , 
        CITY          CHAR(30)         NULL  , 
        STATE         CHAR(2)          NULL  , 
        POSTAL_CODE   CHAR(10)         NULL  , 
        CONSTRAINT PK_ADDRESS PRIMARY KEY (PERSON_NUM, TYPE_CODE)
     )

NOCACHE
LOGGING
/

It is possible, though not advised to create a table without a primary key. I will discuss primary keys on a different page yet to be created. It is also possible to set initial default values for a column though none of the columns in our tables were suitable candidates to do this. There are many good primers on creating tables and databases. The purpose of this page is just to show the basic Oracle syntax by example for creating a table. Also, taking the create table for an existing table and using it as a model is also a very good practice and means of learning.

Share on Google Plus

About Elmirakom

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.