Episode 4 : Adding tables to a database with SQL queries

You can use the CREATE query to add tables to a database. The query begins with the CREATE TABLE statement, as follows:

CREATE TABLE tablename

Then, you add a list of column names with definitions. Separate the information for each column from the information for the following column by a comma. Enclose the entire list in parentheses. Follow each column name by its data type and any other definitions required. The last item in a CREATE TABLEquery indicates which column or combination of columns is the primary key. You specify the primary key by using the following format:

PRIMARY KEY(columnname)

Enclose the columnnamein parentheses. If you’re using a combination of columns as the primary key, include all the column names in the parentheses, separated by commas. For instance, you could designate the primary key as

PRIMARY KEY (columnname1,columnname2).

A complete CREATE TABLE query has the following format:

CREATE TABLE tablename (

columnnamedatatype definition1 definition2 ...,

columnnamedatatype definition1 definition2 ...,

...,

PRIMARY KEY(columnname) )

Listing shows the CREATE TABLE query used to create the Customer table of the CustomerOrderInformation database. You could enter this query on a single line if you wanted to. MySQL doesn’t care how many lines you use. The format shown in Listing  simply makes the query easier for you to read. This human-friendly format also helps you spot typos.

Listing  An SQL Query for Creating a Table

CREATE TABLE Customer (

CustomerID    SERIAL,

lastName      VARCHAR(50),

firstName     VARCHAR(40),

street        VARCHAR(50),

city          VARCHAR(50),

state         CHAR(2),

zip           CHAR(10),

email         VARCHAR(50),

phone         CHAR(15),

fax           CHAR(15),

PRIMARY KEY(customerID) )

Written by ‘Shojib’.