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).

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).

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.

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).

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).

SELECT last_insert_id();

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.

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.

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).

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.