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) );
CREATE TABLE statement has three parts:
TABLEstatement is a free-form table name-in this case
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
||Integer; used for IDs, age, counters, etc.|
||Floating-point number; used for currency, measurements, etc.|
||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.|
||A space-padded, fixed-length text string|
||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
big. For example,
int has variants
Modifiers may be applied to attributes. Two common modifiers are
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 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
All numeric attributes have optional
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.
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
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
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 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
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.