Now, we have created a database and created any users we might wish to have access to this database. We now turn our attention to creating relations (tables) in that database. To create a table, we need to know three things:
SQL Data Types
Although we discussed general data types when talking about how you go about laying out you data, we must now discuss the SQL data types available for use. ANSI SQL defines a number of core types, and each server provides a number of additional types.
In general, our columns are declared by the name of the column, followed by the column's type and some attributes:
ColumnName ColumnType [Attributes]
fullName VARCHAR(200) username VARCHAR(100) NOT NULL user_id INTEGER AUTO_INCREMENT
Tables 9-1 to 9-4 list the key SQL types you will commonly work with, including a couple of non-ANSI SQL types that are seen in key servers, such as Oracle and Microsoft SQL Server.
There are a few attributes that can be placed after the column type to support things such as keys and restrictions on values that are not standardized across servers. The ones we use most frequently throughout this book with MySQL (we show other server equivalents in Appendix B) are:
We will now look at creating tables.
The Database Server Client Lifecycle
To create a table, you must first be connected to the database server as a user with permissions to do so. Once this is done, you then go and tell MySQL which database you wish to work with. This is done via the USE query, as follows. (We have created a database called MessageBoard as an example.)
mysql> USE MessageBoard; Database changed mysql>
We see that there is a common set of actions we always perform when connecting to a database server and working with our data:
Creating the Table
Given both the names and types of the columns that you would like to create, you are ready to execute the CREATE TABLE query, which has the following syntax:
CREATE TABLE TableName( ColumnName ColumnType [attributes], ... ) [attributes or directives];
With the CREATE TABLE query, you specify (in parentheses) a list of the columns to be included in the table, including both their names and types along with any necessary attributes. The attributes we use most frequently are the NOT NULL, AUTO_INCREMENT, and PRIMARY KEY attributes. The first has the database verify that no critical field contains empty values at any point (although we also verify the input and values from within PHP in our web applications). AUTO_INCREMENT has MySQL manage the assignment and incrementing of column values, while PRIMARY KEY designates the given column as the primary key for the table being created. (PRIMARY KEY also implies that the field is NOT NULL.)
For example, for our Messages table, we would execute the following statement given the final design we developed through normalization:
mysql> CREATE TABLE Messages -> ( -> message_id INTEGER AUTO_INCREMENT PRIMARY KEY, -> author_id INTEGER NOT NULL, -> date_posted DATETIME, -> title VARCHAR(150), -> body TEXT, -> forum_id INTEGER NOT NULL -> ); Query OK, 0 rows affected (0.23 sec)
We can also create the tables for the users, replies, and forums:
mysql> CREATE TABLE Users -> ( -> user_id INTEGER AUTO_INCREMENT PRIMARY KEY, -> user_name VARCHAR(50) NOT NULL, -> full_name VARCHAR(150), -> user_email VARCHAR(200) NOT NULL, -> birthdate DATE -> ); Query OK, 0 rows affected (0.18 sec) mysql> CREATE TABLE Replies -> ( -> reply_id INTEGER AUTO_INCREMENT PRIMARY KEY, -> author_id INTEGER NOT NULL, -> message_id INTEGER NOT NULL, -> date_posted DATETIME, -> title VARCHAR(150), -> body TEXT -> ); Query OK, 0 rows affected (0.14 sec) mysql> CREATE TABLE Forums -> ( -> forum_id INTEGER AUTO_INCREMENT PRIMARY KEY, -> name VARCHAR(200) NOT NULL, -> description TEXT, -> owner_id INTEGER -> ); Query OK, 0 rows affected (0.36 sec)
To see a list of all the tables in our database, we can execute the following query (provided we have the permissions):
mysql> SHOW TABLES; +------------------------+ | Tables_in_messageboard | +------------------------+ | Forums | | Messages | | Replies | | Users | +------------------------+ 3 rows in set (0.00 sec)
If we have forgotten some of the details about our table, we can execute the following query to see a list of the columns, their types, and other attributes.
mysql> DESCRIBE Messages; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | message_id | int(11) | | PRI | 0 | | | author_id | int(11) | | | 0 | | | date_posted | datetime | YES | | NULL | | | title | varchar(150) | YES | | NULL | | | body | text | YES | | NULL | | | forum_id | int(11) | | | 0 | | +-------------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
Table Storage Engines
One feature that you will encounter when using the MySQL database server is its support of the ability to store tables in a number of different formats. Each of these formats is managed by a code library called a storage engine. These engines are called from the core MySQL processes and support a number of different features.
The two most common engines you will see and that you will work with are MyISAM (formerly simply called ISAM) and InnoDB. Both of these ship with the freely downloadable MySQL binary releases and support all of the features we will need throughout this book. The key difference between them is that InnoDB supports more robust table and record locking needed for SQL transactions, while the MyISAM engine, not burdened with all of the extra code to support these features, is faster and requires less space for table storage (but cannot be used for transactions and other advanced features).
There are a few other storage engines you might encounter, including BDB (based on the Berkeley Database code libraries managed by Sleepycat Software) and the NDB Cluster engine, which allows for storage of tables across multiple computers.
MySQL works with a "default" storage engine. This is typically the MyISAM engine, but there are installations (notably on Windows) where InnoDB is made the default instead. You can change this value by passing the --default-storage-engine=type option to the server when launching it.
You can specify the storage engine to use when using the CREATE TABLE statement in SQL, as follows:
CREATE TABLE Products ( pid INTEGER AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, price NUMERIC(10,2) NOT NULL, left_in_stock INTEGER NOT NULL ) ENGINE = InnoDB;
The previous table can now be used with transactions in our SQL code (which we will see in the next chapter).
For the Users table we created, we will find that we frequently want the ability to efficiently search through this table to look for records given only the user's name. As mentioned in the earlier section "Indexes for Faster Searching," we can use an index to help. We can do this in MySQL by adding an index to the CREATE TABLE statement, as follows:
INDEX (column1, column2, ..., columnn)
For our Users table, we will create an index on the user_name field as follows:
CREATE TABLE Users ( user_id INTEGER AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(50) NOT NULL, full_name VARCHAR(150), user_email VARCHAR(200) NOT NULL, birthdate DATE, INDEX (user_name) );
If we change the way our application works and later find that we are frequently performing searches on the user_email field, we can create an index after table creation by using the CREATE INDEX statement, which looks as follows:
CREATE INDEX index_name ON TableName (columns)
Thus, we would run the following for our Users table:
CREATE INDEX user_email ON Users (user_email);
Foreign Keys and Cascading Deletes
Taking another look at the Messages and Forums tables we created previously with the CREATE TABLE statement, we see that our table for messages has a foreign key reference to the Forums table, as follows:
forum_id INTEGER NOT NULL
This suggests that we want the values in this column to only be valid identifiers from the forum_id field of the Forums tables. While we could add some code so that whenever a user adds a message, we verified the forum_id was valid, we will instead have the database server do this work for us by having it enforce the foreign keys. This is done by adding a new entry to our CREATE TABLE statement with the following structure:
FOREIGN KEY (my_field) REFERENCES parent_tbl (field)
This statement takes the name of the field in the current table that is to act as a foreign key reference and then takes the name of the parent table and the field that are to identify the referenced values.
All involved tables must be declared as using the InnoDB engine to have MySQL enforce these constraints; otherwise, they are silently ignored.
In the following, we show how to create our Messages table so that the forum_id and the user_id field are both validated and enforced by the database server. Also, we want entries in the Forums table to have their owner_id value validated by the database engine:
CREATE TABLE Users ( user_id INTEGER AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(50) NOT NULL, full_name VARCHAR(150), user_email VARCHAR(200) NOT NULL, birthdate DATE, ) ENGINE = InnoDB; CREATE TABLE Forums ( forum_id INTEGER AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL, description TEXT, owner_id INTEGER, FOREIGN KEY (owner_id) REFERENCES Users (user_id) ) ENGINE = InnoDB; CREATE TABLE Messages ( message_id INTEGER AUTO_INCREMENT PRIMARY KEY, author_id INTEGER NOT NULL, date_posted DATETIME, title VARCHAR(150), body TEXT, forum_id INTEGER NOT NULL, FOREIGN KEY (author_id) REFERENCES Users (user_id), FOREIGN KEY (forum_id) REFERENCES Forums (forum_id) ); CREATE TABLE Replies ( reply_id INTEGER AUTO_INCREMENT PRIMARY KEY, author_id INTEGER NOT NULL, message_id INTEGER NOT NULL, date_posted DATETIME, title VARCHAR(150), body TEXT, FOREIGN KEY (author_id) REFERENCES Users (user_id), FOREIGN KEY (message_id) REFERENCES Messages (message_id) );
Attempting to add a record to the Messages table with a user_id or forum_id field that does not represent a valid identifier from the appropriate table results in an error:
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
However, when we delete a forum, we now have a situation where there are a number of rows in the Messages table that point to a forum that no longer exists. If we are designing a web application where we want the database to automatically delete all of the messages that belong to that forum, we can further modify the FOREIGN KEY constraint we made by having it perform a cascading delete. When a record in the parent (for instance, Forums) table is deleted, any records in the child table where the foreign key reference is set to the ID of the recently deleted parent record are also deleted by the database engine.
We tell the database server we want this to happen by adding ON DELETE CASCADE to the foreign key declaration:
FOREIGN KEY (forum_id) REFERENCES Forums (forum_id) ON DELETE CASCADE