Tables

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.

Creating Tables

First let's learn the syntax. SQL code looks like somewhat stilted English. To create a table, we write

MySQL

 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

NAME	TYPE	MAX. SIZE	AVERAGE SIZE	KEY?
Q	String	255	80	No
PlaID	Int	Size of PlaID	N/A	Foreign (Players)
QuID	Int	4.6 billion	N/A	Primary
DateCreated	Date&Time	N/A	N/A	No
Difficulty	Int	255	N/A	No
Genre	Set	N/A	N/A	No
A 1	String	80	40	No
A 2	String	80	40	No
A n	String	80	40	No
IsTrue 1	Int	1	0 “1	No
IsTrue 2	Int	1	0 “1	No
IsTrue n	Int	1	0 “1	No

Questions table
Q	PlaID	QuID	DateCreated	Difficulty	Genre

Answers table
QuID	A	IsTrue

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.

New Questions table
Q	PlaID	QuID	DateCreated	Difficulty

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

 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.

NOTE

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

 mysql> describe  <table_name>; 

The describe command returns a table, with a row for each column, that looks like this:

Field	Type	Null	Key	Default	Extra
Column Name	Var. Type	Is Null OK?	Primary ?	if null	Constraints

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.

Output

 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.

Altering Tables

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

 mysql> ALTER TABLE  <Table_Name> [ADD,MODIFY]  <Column_Name>  <DataType> <Flags>; 

In addition, we can rename the column of a table.

MySQL

 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

 mysql> ALTER TABLE  <Table_Name> DROP  <Column_Name>; 

We fix the Answers table as follows:

MySQL

 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

 mysql> SHOW TABLES;