Designing and Creating Tables - Database Design Manual: Using MySQL for Windows - page 39

Figure 4.9 DESCRIBE the newly created table.

Figure 4.9 shows the results of this. You can see that the table has been created, and the
ID column is the primary key and there is an auto-increment column.
You will notice that after we defined the list of columns we added the PRIMARY KEY
option, which set the column that we specified as the primary key for this table. You can also
specify a column as a primary key by putting these words directly after the type definition
for a column.
We will demonstrate this by creating another table for the website log as follows:

CREATE TABLE Log ( ID MEDIUMINT
NOT NULL
AUTO_INCREMENT
PRIMARY KEY,
CookieID MEDIUMINT,
WebpageID MEDIUMINT,
Browser VARCHAR(50),
DateCreated DATETIME,
IPNumber VARCHAR(15),
ReferringPage VARCHAR(255) )

At this stage I've decided to store the visitor browser information within the log file, and
not as a separate table. We are, however, only storing the IP number of the client machine.
If we need to find the host name we can look it up using a DNS server.

Identifying Foreign Keys

Our webpage table did not have any foreign keys, but our log table does. To define the for-
eign key we have to insert the following into our create table definition:

FOREIGN KEY (column) REFERENCES parenttable (primarycolumn)

In this command:

•

column is the name of the column that contains the foreign key in this table.

•

primarycolumn is the name of the primary key column that this foreign key references.