Create SQL Table Statement

The table that we will use for the examples in this module will contain a customer's record, that is, the customer's information that includes name, address, and phone number, along with a unique key for the information. Here's an overview of the information you want to capture:

1. Name
2. Address
3. Phone number

From this, you can surmise that what you will really need to capture, from a database stance, is the following:

1. Customer ID
2. First name
3. Last name
4. Address (two lines)
5. City, state, zip code
6. Phone number, including area code

Before you actually create the table, let us examine column data types more closely in the next lesson.

The CREATE TABLE statement builds a database table. The basic syntax for creating a table is:

CREATE TABLE table_name (parameters)

Here table_name is the name you want to give to the new table and parameters is a series of statements that define the table's columns. Optionally parameters can include column-level and table-level constraints. A column definition includes the column's name, its data type, and optional extras such as a default value or the keywords NULL or NOT NULL to indicate whether the column should allow null values.A particularly useful option that you can add to the CREATE TABLE statement is IF NOT EXISTS. This clause makes the statement create the new table only if it does not already exist. For example, the following statement creates a Students table with three fields. Notice how the code uses whitespace to make the data types and NOT NULL clauses align so they are easier to read:

CREATE TABLE IF NOT EXISTS Students (
idStudent INT NOT NULL AUTO_INCREMENT,
FirstName VARCHAR(45) NOT NULL,
LastName VARCHAR(45) NOT NULL,
PRIMARY KEY (idStudent)
)

The idStudent field is an integer (INT) that is required (NOT NULL). The database automatically generates values for this field by adding one to the value it last generated (AUTO_INCREMENT). The FirstName and LastName fields are required variable-length strings up to 45 characters long. The table's primary key is the idStudent field.