Managing Databases, Tables, and Indexes (Web Database Applications with PHP & MySQL)

3.4.2. Creating Tables

After issuing the use winestore command, you then usually issue commands to create the tables in the database, as shown in Example 3-1. (You already created the tables in the winestore database in Section 3.2 of this chapter). Let's look at one of these tables, the customer table. The statement that created this table is shown in Example 3-2.

Example 3-2. Creating the customer table with SQL

CREATE TABLE customer (
  cust_id int(5) DEFAULT '0' NOT NULL auto_increment,
  surname varchar(50) NOT NULL,
  firstname varchar(50) NOT NULL,
  initial char(1),
  title varchar(10),
  addressline1 varchar(50) NOT NULL,
  addressline2 varchar(50),
  addressline3 varchar(50),
  city varchar(20) NOT NULL,
  state varchar(20),
  zipcode varchar(5),
  country varchar(20) DEFAULT 'Australia',
  phone varchar(15),
  fax varchar(15),
  email varchar(30) NOT NULL,
  salary int(7),
  birth_date date( ),
  PRIMARY KEY (cust_id),
  KEY names (surname,firstname)
);

The CREATE TABLE statement has three parts:

Following the CREATE TABLE statement is a free-form table name—in this case customer.

Following an opening bracket is a list of attribute names, types, and modifiers.
After the attribute list is a list of keys; that is, information defining what attributes satisfy the uniqueness constraints of a primary key and what attributes are to be indexed for fast access.

A table name may contain any character except a forward slash / or a period, and the name is usually the name of an entity created in the ER model. Attribute names may contain any character, and there are many possible data types for attributes. Details of selected commonly used types are shown in Table 3-1.

Table 3-1. Common SQL data types for attributes

Data type	Comments
int(`length`)	Integer; used for IDs, age, counters, etc.
float(`length``,decimals`)	Floating-point number; used for currency, measurements, etc.
timestamp(`length`)	Updates each time the row is modified or can be manually set. A length of 14 (the default) displays an attribute containing date and time in the format YYYYMMDDHHMMSS. Length 12 displays YYMMDDHHMMSS, 8 displays YYYYMMDD, and 6 displays YYMMDD.
char(`length`)	A space-padded, fixed-length text string.
varchar(`length`)	An unpadded, variable-length text string with a specified maximum length.
blob	An attribute that stores up to 64 KB of data.

For situations where the data stored is always much smaller or larger than the maximum possible value, many attribute types have variants of tiny, small, medium, and big. For example, int has variants smallint, mediumint, and bigint.

Modifiers may be applied to attributes. Two common modifiers are NOT NULL—data can't be added without this attribute having a value—and DEFAULT, which sets the data to the value that follows when no data is supplied.

Identifier attributes—an example in the customer table is the cust_id attribute—often have the modifier auto_increment. The auto_increment modifier automatically writes a unique number into an attribute when no value is supplied. For example, if you insert 10 customer rows into the customer table, you can automatically generate a cust_id of 11 by inserting NULL (or zero) as the value for cust_id. Only one attribute in each table can have the auto_increment modifier.

All numeric attributes have optional zerofill and unsigned modifiers. The former left-pads a value with zeros up to the size of the attribute type. The latter allows only positive values to be stored and roughly doubles the maximum positive value that can be stored.

Inserting NULL into a TIMESTAMP (or another date or time type) attribute stores the current date and time. What is stored in the attribute depends on its length. For example, if the attribute has the type TIMESTAMP(12), both the time and date are stored in the format YYMMDDHHMMSS. If today is January 3, 2002 and time is 10:43:23, the value stored is 020103104323.

More details on attribute types and modifiers can be found in Section 7.7 of the manual.html file distributed with MySQL.

The final component of the CREATE TABLE statement is a specification of key constraints and indexes that are required. In Example 3-2, we specify that the unique identifier is the cust_id attribute by adding the statement PRIMARY KEY (cust_id). The PRIMARY KEY constraint has two restrictions: the attribute must be defined as NOT NULL, and any value inserted must be unique. It is good practice to explicitly state a PRIMARY KEY for all tables; determining primary keys from an ER model is discussed in Appendix C.

We also show in our example another KEY definition; KEY is a synonym for INDEX. In this case, we have defined a KEY names (surname, firstname) to permit fast access to data stored in the customer table by a combination of surname and firstname values. In many cases—without yet knowing what kinds of queries will be made on the database—it is difficult to determine what indexes should be specified. MySQL permits up to 16 indexes to be created on any table, but unnecessary indexes should be avoided. Each index takes additional space, and it must be updated as the data stored in the table is inserted, deleted, and modified. We discuss index tuning in Section 3.10.

Example 3-2. Creating the customer table with SQL

Table 3-1. Common SQL data types for attributes

3.4. Managing Databases, Tables, and Indexes

3.4.1. Creating Databases

3.4.2. Creating Tables

3.4.3. Altering Tables and Indexes

3.4.4. Displaying Database Structure with SHOW