Keys, Indexes, and Referential Integrity

Primary keys uniquely identify records in the database. These keys can be thought of as the fingerprint of a record. Any number, such as a phone number, can be used as the value for the key, as long as it’s unique. These types of keys are called natural keys. Natural keys are numbers or values that exist naturally in the world and can be used to uniquely identify somebody or something. Using natural keys can spell trouble, though, because once the primary key has been set, it cannot be updated in the future. Therefore, if a phone number is used to represent the key, and that number changes, nothing short of deleting and reinserting that record can be done. Synthetic keys are system-generated numbers, sometimes created by the database itself, which could be a number sequence or globally unique identifier (GUID). Synthetic key values, sometimes called dataless keys, offer an advantage because their value has no meaning to any person or to the application itself. Figure 9.1 shows two database tables, player and item. Each column contains a column named id, which will be used to uniquely identify players and items.

Figure 9.1: Player and item database tables.

Foreign keys act as pointers, or references, to the primary keys of records in other tables. Foreign keys maintain the relationships between tables, making the database relational. The tables and their relationships make up the structure of the database system, referred to as the database schema. Figure 9.2 shows the foreign key relationship between the two tables.

Figure 9.2: Foreign key relationship.

Indexes are small, system-generated tables that contain a summary (or subset) of data found in a complete table. Indexing works in a similar manner to the index of a book. When indexes are used, only the relevant data is traversed during a search. Tables can get large and hold many thousands (or millions) of records. The database will check for, and use, any indexes available to it. Indexes are a must for any large database, but the process does slow down data inserts, because the index tables must be written to in addition to the other tables. However, the indexes balance the queries, and overall performance is increased drastically. Without indexes, performance could be potentially dreadful, because searches would involve complete table scans. Primary keys and foreign keys are almost always indexed, but other columns that are frequently used in searches may be indexed as well.

Referential Integrity

Relational databases relate data. Referential integrity is the enforcement of these relationships. Java allows an object to hold a reference to another object that is null, right? Well, the database can do the same. If allowed, the database will store records that contain foreign keys into other tables, even if the foreign keys don’t reference any valid existing record. For a Java object, using such a reference would result in disaster, and a NullPointerException would be thrown. The database does something similar, and neither is desired. However, if the database does enforce referential integrity, it will not allow keys to point to null data. If referential integrity is enforced, all foreign keys in the record must point to valid records in the database; otherwise, the database will throw an error. This constrains the system, keeping the relational structure intact.

Occasionally, it is necessary to build a database in a generic way, throwing caution and referential integrity to the wind. Generic in this case means that no structure is imposed on the data. Why would someone want this? It’s sometimes desirable to build database tables that store data generically, similar to a base class in Java. In this circumstance, a table may store many different kinds of records. Making multipurpose tables can be an attractive solution for larger databases, which can get very ugly very quickly, holding vast amounts of data in numerous tables that have complex relationships. Based on the kind of information written into that table, key relationships may or may not exist. Therefore, to do this, referential integrity must be disabled. Again, with it enabled, key references cannot be null. In spite of this, enforcing referential integrity is always advised, and there should be a really good reason for not using it. Although generic tables can keep the number of tables down, table size, as well as the complexity of the queries, increases.

Online Resources

JDBC Home Page: http://java.sun.com/products/jdbc

JDBC Download Page: http://java.sun.com/products/jdbc/download.html

JDBC Learning Center: http://java.sun.com/products/jdbc/learning.html

JDBC FAQ: http://java.sun.com/products/jdbc/faq.html

JDBC Driver Search: http://servlet.java.sun.com/products/jdbc/drivers