There are generally two ways to create database tables:
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).
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
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:
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;
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).
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.
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:
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;
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:
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;
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.
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 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.
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.
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.)
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:
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:
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;
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.
Functions Are Not Allowed Unlike most DBMSs, MySQL does not allow the use of functions as DEFAULT values; only constants are supported.
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.
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:
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).
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.