One of the first things we learn to do in SQL is to create tables. It doesn't make any sense to try to store information or retrieve records if there is no table to keep the records in. Fortunately, the syntax is very simple. Unfortunately, the creation of tables is a discipline that, like all programming, comes partly from book learning but mostly from practice.
First let's learn the syntax. SQL code looks like somewhat stilted English. To create a table, we write
mysql> CREATE TABLE <table_name> ( -> <first_column_name> <datatype> <special_flags>, -> <second_column_name> <datatype> <special_flags>, ... -> PRIMARY_KEY (<first_column_in_key>, <second_column>, ...) -> );
We then tell MySQL what we want it to do, namely, create a table. Then we follow with the name of the table we wish to create. The next step is where the most care is required. In parentheses, we list the name of the column, followed by the type of column, followed by any special flags, all separated by spaces. Commas separate each column definition.
Let's create a table of questions. After consulting with the designers of Quiz, we decide that a question should consist of the items shown in Table 12.1. The question has an unlimited number of answers, so putting them in the same table as Q (the text of the question) and the QuID (question unique identifier) doesn't make sense. If we remember what we discussed in the last chapter, we break the question definition into two tables, one solely of answers and one filled with all the other information.
Table 12.1. Planned Questions Table Organization
In the two tables, the names mean the same and have the same properties as Table 12.1 spells out. Since no one wants to actually think of genres that make up the set right now, we just leave out the genre column and add it later.
Take a few minutes to see if you can create the code necessary to create the Questions and Answers tables. Then move on to the following code.
mysql> CREATE TABLE Questions ( -> Q VARCHAR(255), -> PlaID MEDIUMINT UNSIGNED, -> QuID INT UNSIGNED NOT NULL AUTO_INCREMENT, -> Created DATE, -> Difficulty TINYINT, -> PRIMARY KEY (QuID), -> FOREIGN KEY (PlaID) REFERENCES Players -> ON UPDATE RESTRICT -> ON DELETE SET NULL -> ); Query OK, 0 rows affected (0.08 sec) mysql > CREATE TABLE Answers ( -> QuID MEDIUMINT NOT NULL, -> A VARCHAR(80) NOT NULL, -> IsTrue TINYINT NOT NULL DEFAULT 0, -> PRIMARY KEY (QuID, A), -> FOREIGN KEY (QuID) REFERENCES Questions -> ON UPDATE RESTRICT -> ON DELETE CASCADE -> ); Query OK, 0 rows affected (0.00 sec)
As we can see, this code creates Questions and Answers tables. The two tables are linked by the QuID, which is a primary key in the Questions table and a foreign key in the Answers table that references the Questions.
Note that the primary key of the Questions table is a combination of QuID and the answer itself. It is obvious that QuID s repeat and that multiple questions may have "Oort Cloud" as their answer. Okay, maybe not "Oort Cloud", but "Venus" certainly seems plausible. However, it should not be acceptable for "Venus" to be an answer multiple times for any given question. Ergo the primary key is a combination of the QuID and the answer's text.
M Y SQL F OREIGN K EY I MPLEMENTATION
Unfortunately these foreign key declarations are ignored by MySQL version 3.23. But, since other SQL implementations and future releases of MySQL may have this ability, foreign keys are included in the code. One implementation that currently is free and supports foreign keys is PostGreSQL.
Reviewing a Table Structure
Sometimes after we create a table we forget exactly how we set the table up. The solution to this problem is the describe keyword that follows this syntax:
mysql> describe <table_name>;
The describe command returns a table, with a row for each column, that looks like this:
This function is helpful when we forget how we set up our tables or when we start work on someone else's system.
Let's use describe to review the Answers table we created earlier.
mysql> DESCRIBE Answers; +---------------+--------------+------+-----+---------+-------+ Field Type Null Key Default Extra +---------------+--------------+------+-----+---------+-------+ QuID mediumint(9) PRI 0 AnswerChoice varchar(80) PRI IsTrue tinyint(4) 0 +---------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
Note that the foreign key ( QuID ) is signed. This may cause errors since the foreign key does not match the primary key of the Questions table. We need to alter the Answers table.
We just created a table that holds the questions, and at the time of creation we forgot to set one of the columns to unsigned. Here's how we fix our mistake. First, let's look at the alter command. We can make four kinds of changes to a table (there are more, but they're unimportant). We can add a column. We can also modify a column. In both cases the code looks the same.
mysql> ALTER TABLE <Table_Name> [ADD,MODIFY] <Column_Name> <DataType> <Flags>;
In addition, we can rename the column of a table.
mysql> ALTER TABLE <Table_Name> CHANGE <Old_Column_Name> <New_Column_Name> <DataType> <Flags>;
Last, we can drop a column. It is important to keep in mind that deleting a column permanently and irrevocably deletes all the data within the column.
mysql> ALTER TABLE <Table_Name> DROP <Column_Name>;
We fix the Answers table as follows:
mysql> ALTER TABLE Answers MODIFY QuID INT UNSIGNED;
Someday, when we forget what we called this table or whether Answers is capitalized or not (MySQL table names are case-sensitive), we will be thankful for the show tables command:
mysql> SHOW TABLES;