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.
ColumnName ColumnType [Attributes]
fullName VARCHAR(200) username VARCHAR(100) NOT NULL user_id INTEGER AUTO_INCREMENT
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:
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)
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)
mysql> SHOW TABLES; +------------------------+ | Tables_in_messageboard | +------------------------+ | Forums | | Messages | | Replies | | Users | +------------------------+ 3 rows in set (0.00 sec)
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;
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)
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) );
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.
FOREIGN KEY (forum_id) REFERENCES Forums (forum_id) ON DELETE CASCADE