• Choose a category
• All
• Classic-Fiction

Primary keys are important because you use them as a standard index for the table. The job of a primary key is to uniquely identify each record in the table. Remember that a primary key has a few important characteristics:

♦ It must exist. Every record must have a primary key.

♦ It must be unique. Two records in the same table can’t have the same key.

♦ It must not be null. There must be a value in each key.

When you initially create a table, you have all the values in front of you, but what if you want to add a field later? Somehow, you have to ensure that the primary key in every record is unique.

Over the years, database developers have discovered that integer values are especially handy as primary keys. The great thing about integers is that you can always find a unique one. Just look for the largest index in your table and add one.

Fortunately, MySQL (like most database packages) has a wonderful feature for automatically generating unique integer indices.

Latin-Swedish?

phpMyAdmin is a wonderful tool, but it does have one strange quirk. When you look over your table design, you may find that the collation is set to latin1_swedish_ci. This syntax refers to the native character set used by the internal data structure. Nothing is terribly harmful about this set (Swedish is a wonderful language), but I don’t want to incorrectly imply that my database is written in Swedish.

Fortunately, it’s an easy fix. In phpMyAdmin, go to the Operations tab and look for Table Options. You can then set your collation to whatever you want. I typically use latin1_general_ci as it works fine for American English, which is the language used in most of my data sets. (See the MySQL documentation about internationalization if you’re working in a language that needs the collation feature.)

I’ve only run into this problem with phpMyAdmin. If you create your database directly from the MySQL interpreter or from within PHP programs, the collation issue doesn’t seem to be a problem.

Take a look at this variation of the buildContact.sql script:

-- buildContactAutoIncrement.sql

DROP TABLE IF EXISTS contact;

CREATE TABLE contact (

contactID int PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(50),

company VARCHAR(30),

email VARCHAR(50)

);

INSERT INTO contact VALUES

(null, ‘Bill Gates‘, ‘Microsoft‘, ‘bill@msBob.com‘);

INSERT INTO contact VALUES

(null, ‘Steve Jobs‘, ‘Apple‘, ‘steve@rememberNewton.com‘);

INSERT INTO contact VALUES

(null, ‘Linus Torvalds‘, ‘Linux Foundation‘, ‘linus@gnuWho.org‘);

INSERT INTO contact VALUES

(null, ‘Andy Harris‘, ‘Wiley Press‘, ‘andy@aharrisBooks.net‘);

SELECT * FROM contact;

Here are the changes in this script:

♦ Add the AUTO_INCREMENT tag to the primary key definition. This tag indicates that the MySQL system will automatically generate a unique integer for this field. You can apply the AUTO_INCREMENT tag to any field, but you most commonly apply it to primary keys.

♦ Replace index values with null. When you define a table with AUTO_INCREMENT, you should no longer specify values in the affected field. Instead, just place the value null. When the SQL interpreter sees the value null on an AUTO_INCREMENT field, it automatically finds the next largest integer.

You may wonder why I’m entering the value null when I said primary keys should never be null. Well, I’m not really making them null. The null value is simply a signal to the interpreter: “Hey, this field is AUTO_INCREMENT, and I want you to find a value for it.”

Selecting Data from Your Tables

Creating a database is great, but the real point of a database is to extract information from it. SQL provides an incredibly powerful command for retrieving data from the database. The basic form looks as follows:

SELECT * FROM contact;

The easiest way to practice SQL commands is to use phpMyAdmin. Figure 2-5 shows phpMyAdmin with the SQL tab open.

Figure 2-5: You can easily test queries in phpMyAdmin.

Note that you can enter SQL code in multiple places. If you’re working with