Creating Tables

MySQL statements are not used just for table data manipulation. Indeed, MySQL can be used to perform all database and table operations, including the creation and manipulation of tables themselves.

There are generally two ways to create database tables:

• Using an administration tool (like the ones discussed in Chapter 2, "Introducing MySQL") that can be used to create and manage database tables interactively.

• Tables may also be manipulated directly with MySQL statements.

To create tables programmatically, the CREATE TABLE SQL statement is used. It is worth noting that when you use interactive tools, you are actually using MySQL statements. Instead of your writing these statements, however, the interface generates and executes the MySQL seamlessly for you (the same is true for changes to existing tables).

Note

Additional Examples For additional examples of table creation scripts, see the code used to create the sample tables used in this book.

Basic Table Creation

To create a table using CREATE TABLE, you must specify the following information:

• The name of the new table specified after the keywords CREATE TABLE.

• The name and definition of the table columns separated by commas.

The CREATE TABLE statement can also include other keywords and options, but at a minimum you need the table name and column details. The following MySQL statement creates the customers table used throughout this book:

• Input

CREATE TABLE customers (   cust_id      int       NOT NULL AUTO_INCREMENT,   cust_name    char(50)  NOT NULL ,   cust_address char(50)  NULL ,   cust_city    char(50)  NULL ,   cust_state   char(5)   NULL ,   cust_zip     char(10)  NULL ,   cust_country char(50)  NULL ,   cust_contact char(50)  NULL ,   cust_email   char(255) NULL ,   PRIMARY KEY (cust_id) ) ENGINE=InnoDB;

• Analysis

As you can see in the preceding statement, the table name is specified immediately following the CREATE TABLE keywords. The actual table definition (all the columns) is enclosed within parentheses. The columns themselves are separated by commas. This particular table is made up of nine columns. Each column definition starts with the column name (which must be unique within the table), followed by the column's datatype. (Refer to Chapter 1, "Understanding SQL," for an explanation of datatypes. In addition, Appendix D, "MySQL Datatypes," lists the datatypes supported by MySQL.) The table's primary key may be specified at table creation time using the PRIMARY KEY keywords; here, column cust_id is specified as the primary key column. The entire statement is terminated with a semicolon after the closing parenthesis. (Ignore the ENGINE=InnoDB and AUTO_INCREMENT statements for now; we'll come back to that later).

Tip

Statement Formatting As you will recall, whitespace is ignored in MySQL statements. Statements can be typed on one long line or broken up over many lines. It makes no difference at all. This enables you to format your SQL as best suits you. The preceding CREATE TABLE statement is a good example of MySQL statement formattingthe code is specified over multiple lines, with the column definitions indented for easier reading and editing. Formatting your MySQL in this way is entirely optional, but highly recommended.

Tip

Handling Existing Tables When you create a new table, the table name specified must not exist or you'll generate an error. To prevent accidental overwriting, SQL requires that you first manually remove a table (see later sections for details) and then re-create it, rather than just overwriting it.

If you want to create a table only if it does not already exist, specify IF NOT EXISTS after the table name. This does not check to see that the schema of the existing table matches the one you are about to create. It simply checks to see if the table name exists, and only proceeds with table creation if it does not.

Working with NULL Values

Back in Chapter 6, "Filtering Data," you learned that NULL values are no values or the lack of a value. A column that allows NULL values also allows rows to be inserted with no value at all in that column. A column that does not allow NULL values does not accept rows with no valuein other words, that column will always be required when rows are inserted or updated.

Every table column is either a NULL column or a NOT NULL column, and that state is specified in the table definition at creation time. Take a look at the following example:

• Input

CREATE TABLE orders (   order_num  int      NOT NULL AUTO_INCREMENT,   order_date datetime NOT NULL ,   cust_id    int      NOT NULL ,   PRIMARY KEY (order_num) ) ENGINE=InnoDB;

• Analysis

This statement creates the orders table used throughout this book. orders contains three columns: order number, order date, and the customer ID. All three columns are required, and so each contains the keyword NOT NULL. This will prevent the insertion of columns with no value. If someone tries to insert no value, an error will be returned, and the insertion will fail.

This next example creates a table with a mixture of NULL and NOT NULL columns:

• Input

CREATE TABLE vendors (   vend_id      int      NOT NULL AUTO_INCREMENT,   vend_name    char(50) NOT NULL ,   vend_address char(50) NULL ,   vend_city    char(50) NULL ,   vend_state   char(5)  NULL ,   vend_zip     char(10) NULL ,   vend_country char(50) NULL ,   PRIMARY KEY (vend_id) ) ENGINE=InnoDB;

• Analysis

This statement creates the vendors table used throughout this book. The vendor ID and vendor name columns are both required, and are, therefore, specified as NOT NULL. The five remaining columns all allow NULL values, and so NOT NULL is not specified. NULL is the default setting, so if NOT NULL is not specified, NULL is assumed.

Caution

Understanding NULL Don't confuse NULL values with empty strings. A NULL value is the lack of a value; it is not an empty string. If you were to specify '' (two single quotes with nothing in between them), that would be allowed in a NOT NULL column. An empty string is a valid value; it is not no value. NULL values are specified with the keyword NULL, not with an empty string.

Primary Keys Revisited

As already explained, primary key values must be unique. That is, every row in a table must have a unique primary key value. If a single column is used for the primary key, it must be unique; if multiple columns are used, the combination of them must be unique.

The CREATE TABLE examples seen thus far use a single column as the primary key. The primary key is thus defined using a statement such as

PRIMARY KEY (vend_id)

To create a primary key made up of multiple columns, simply specify the column names as a comma delimited list, as seen in this example:

CREATE TABLE orderitems (   order_num  int          NOT NULL ,   order_item int          NOT NULL ,   prod_id    char(10)     NOT NULL ,   quantity   int          NOT NULL ,   item_price decimal(8,2) NOT NULL ,   PRIMARY KEY (order_num, order_item) ) ENGINE=InnoDB;

The orderitems table contains the order specifics for each order in the orders table. There may be multiple items per order, but each order will only ever have one first item, one second item, and so on. As such, the combination of order number (column order_num) and order item (column order_item) are unique, and thus suitable to be the primary key which is defined as

PRIMARY KEY (order_num, order_item)

Primary keys may be defined at table creation time (as seen here) or after table creation (as will be discussed later in this chapter).

Tip

Primary Keys and NULL Values Back in Chapter 1, you learned that primary keys are columns whose values uniquely identify every row in a table. Only columns that do not allow NULL values can be used in primary keys. Columns that allow no value at all cannot be used as unique identifiers.

Using AUTO_INCREMENT

Let's take a look at the customers and orders tables again. Customers in the customers table are uniquely identified by column cust_id, a unique number for each and every customer. Similarly, orders in the orders table each have a unique order number which is stored in column order_num.

These numbers have no special significance, other than the fact that they are unique. When a new customer or order is added, a new customer ID or order number is needed. The numbers can be anything, so long as they are unique.

Obviously, the simplest number to use would be whatever comes next, whatever is one higher than the current highest number. For example, if the highest cust_id is 10005, the next customer inserted into the table could have a cust_id of 10006.

Simple, right? Well, not really. How would you determine the next number to be used? You could, of course, use a SELECT statement to get the highest number (using the Max() function introduced in Chapter 12, "Summarizing Data") and then add 1 to it. But that would not be safe (you'd need to find a way to ensure that no one else inserted a row in between the time that you performed the SELECT and the INSERT, a legitimate possibility in multi-user applications). Nor would it be efficient (performing additional MySQL operations is never ideal).

And that's where AUTO_INCREMENT comes in. Look at the following line (part of the CREATE TABLE statement used to create the customers table)

cust_id      int      NOT NULL AUTO_INCREMENT,

AUTO_INCREMENT tells MySQL that this column is to be automatically incremented each time a row is added. Each time an INSERT operation is performed MySQL automatically increments (and thus AUTO_INCREMENT) the column, assigning it the next available value. This way each row is assigned a unique cust_id which is then used as the primary key value.

Only one AUTO_INCREMENT column is allowed per table, and it must be indexed (for example, by making it a primary key).

Note

Overriding AUTO_INCREMENT Need to use a specific value if a column designated as AUTO_INCREMENT? You cansimply specify a value in the INSERT statement and as long as it is unique (has not been used yet), that value will be used instead of an automatically generated one. Subsequent incrementing will start using the value manually inserted. (See the table population scripts used in this book for examples of this.)

Tip

Determining the AUTO_INCREMENT Value One downside of having MySQL generate (via auto increment) primary keys for you is that you don't know what those values are.

Consider this scenario: You are adding a new order. This requires creating a single row in the orders table and then a row for each item ordered in the orderitems table. The order_num is stored along with the order details in orderitems. This is how the orders and orderitems table are related to each other. And that obviously requires that you know the generated order_num after the orders row was inserted and before the orderitems rows are inserted.

So how could you obtain this value when an AUTO_INCREMENT column is used? By using the last_insert_id() function, like this:

SELECT last_insert_id();

This returns the last AUTO_INCREMENT value, which you can then use in subsequent MySQL statements.

Specifying Default Values

MySQL enables you to specify default values to be used if no value is specified when a row is inserted. Default values are specified using the DEFAULT keyword in the column definitions in the CREATE TABLE statement.

Look at the following example:

• Input

CREATE TABLE orderitems (   order_num  int          NOT NULL ,   order_item int          NOT NULL ,   prod_id    char(10)     NOT NULL ,   quantity   int          NOT NULL  DEFAULT 1,   item_price decimal(8,2) NOT NULL ,   PRIMARY KEY (order_num, order_item) ) ENGINE=InnoDB;

• Analysis

This statement creates the orderitems table that contains the individual items that make up an order. (The order itself is stored in the orders table.) The quantity column contains the quantity for each item in an order. In this example, adding the text DEFAULT 1 to the column description instructs MySQL to use a quantity of 1 if no quantity is specified.

Caution

Functions Are Not Allowed Unlike most DBMSs, MySQL does not allow the use of functions as DEFAULT values; only constants are supported.

Tip

Using DEFAULT Instead of NULL Values Many database developers use DEFAULT values instead of NULL columns, especially in columns that will be used in calculations or data groupings.

Engine Types

You might have noticed that the CREATE TABLE statements used thus far all ended with an ENGINE=InnoDB statement.

Like every other DBMS, MySQL has an internal engine that actually manages and manipulates data. When you use the CREATE TABLE statement, that engine is used to actually create the tables, and when you use the SELECT statement or perform any other database processing, the engine is used internally to process your request. For the most part, the engine is buried within the DBMS and you need not pay much attention to it.

But unlike every other DBMS, MySQL does not come with a single engine. Rather, it ships with several engines, all buried within the MySQL server, and all capable of executing commands such as CREATE TABLE and SELECT.

So why bother shipping multiple engines? Because they each have different capabilities and features, and being able to pick the right engine for the job gives you unprecedented power and flexibility.

Of course, you are free to totally ignore database engines. If you omit the ENGINE= statement, the default engine is used (most likely MyISAM), and most of your SQL statements will work as is. But not all of them will, and that is why this is important (and why two engines are used in the same tables used in this book).

Here are several engines of which to be aware:

• InnoDB is a transaction-safe engine (see Chapter 26, "Managing Transaction Processing"). It does not support full-text searching.

• MEMORY is functionally equivalent to MyISAM, but as data is stored in memory (instead of on disk) it is extremely fast (and ideally suited for temporary tables).

• MyISAM is a very high-performance engine. It supports full-text searching (see Chapter 18, "Full-Text Searching"), but does not support transactional processing.

Note

To Learn More For a complete list of supported engines (and the differences between them), see http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html.

Engine types may be mixed. The example tables used throughout this book all use InnoDB with the exception of the productnotes table, which uses MyISAM. The reason for this is that I wanted support for transactional processing (and thus used InnoDB) but also needed full-text searching support in productnotes (and thus MyISAM for that table).

Caution

Foreign Keys Can't Span Engines There is one big downside to mixing engine types. Foreign keys (used to enforce referential integrity, as explained in Chapter 1, "Understanding SQL") cannot span engines. That is, a table using one engine cannot have a foreign key referring to a table that uses another engine.

So, which should you use? Well, that will depend on what features you need. MyISAM tends to be the most popular engine because of its performance and features. But if you do need transaction-safe processing, you will need to use another engine.