MySQL - 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 IF NOT EXISTS person
     (
       num            INT(11)      NOT NULL  DEFAULT 0  , 
       firstname      VARCHAR(20)      NULL             , 
       gender_code    CHAR(1)          NULL             , 
       birth_dttm     DATETIME         NULL             , 
       inactive_date  DATETIME         NULL             , 
       lastname       VARCHAR(30)      NULL             , 
       PRIMARY KEY (num) 
     ) ENGINE = MYISAM

GO

Creating the phone table

CREATE TABLE IF NOT EXISTS phone
     (
       person_num  INT(11)  NOT NULL  DEFAULT 0  , 
       type_code   CHAR(3)  NOT NULL             , 
       area_code   CHAR(3)      NULL             , 
       exchange    CHAR(3)      NULL             , 
       extension   CHAR(4)      NULL             , 
       PRIMARY KEY (person_num, type_code) 
     ) ENGINE = MYISAM

GO

Creating the address table

CREATE TABLE IF NOT EXISTS address
     (
       person_num   INT(11)   NOT NULL  DEFAULT 0  , 
       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             , 
       PRIMARY KEY (person_num, type_code) 
     ) ENGINE = MYISAM

GO

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 MySQL 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.

 Collapse All    Expand All
Custom Search

MySQL Information and Resources