Using AUTO_INCREMENT for Primary Keys in Java

Make PDF417 in Java Using AUTO_INCREMENT for Primary Keys

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 Take a look at this variation of the buildContactsql script:

-- buildContactAutoIncrementsql 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@msBobcom ); INSERT INTO contact VALUES (null, Steve Jobs , Apple , steve@rememberNewtoncom ); INSERT INTO contact VALUES (null, Linus Torvalds , Linux Foundation , linus@gnuWhoorg ); INSERT INTO contact VALUES (null, Andy Harris , Wiley Press , andy@aharrisBooksnet ); 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

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

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 like

SELECT * FROM contact;

The easiest way to practice SQL commands is to use phpMyAdmin Figure 2-5 shows phpMyAdmin with the SQL tab open Note that you can enter SQL code in multiple places If you re working with a particular table, you can invoke that table s SQL tab (as I do in Figure 2-5) You can also always enter SQL code into your system with the SQL button on the main phpMyAdmin panel (on the left panel of all phpMyAdmin screens) If you have a particular table currently active, the SQL dialog box shows you the fields of the current table, which can be handy when you write SQL queries Try the SELECT * FROM contact; code in the SQL dialog box, and you see the results shown in Figure 2-6