When Runtime.exec() won't Navigate yourself around pitfalls related to the Runtime.exec() method
Summary In this installment of Java Traps, Michael Daconta discusses one new pitfall and revisits another from his previous column. Originating in the java.lang package, the pitfall specifically involves problems with the Runtime.exec() method. Daconta also corrects an error from Pitfall 3 and offers a simpler solution. (2,500 words) By Michael C. Daconta
s part of the Java language, the java.lang package is implicitly imported into every Java program. This package's pitfalls surface often, affecting most programmers. This month, I'll discuss the traps lurking in the Runtime.exec() method.
Pitfall 4: When Runtime.exec() won't The class java.lang.Runtime features a static method called getRuntime(), which retrieves the current Java Runtime Environment. That is the only way to obtain a reference to the Runtime object. With that reference, you can run external programs by invoking the Runtime class's exec() method. Developers often call this method to launch a browser for displaying a help page in HTML.
There are four overloaded versions of the exec() command:
public Process exec(String command);
public Process exec(String [] cmdArray);
public Process exec(String command, String [] envp);
public Process exec(String [] cmdArray, String [] envp);
For each of these methods, a command -- and possibly a set of arguments -- is passed to an operating-system-specific function call. This subsequently creates an operating-system-specific process (a running program) with a reference to a Process class returned to the Java VM. The Process class is an abstract class, because a specific subclass of Process exists for each operating system.
You can pass three possible input parameters into these methods:
A single string that represents both the program to execute and any arguments to that program
An array of strings that separate the program from its arguments
An array of environment variables
Pass in the environment variables in the form name=value. If you use the version of exec() with a single string for both the program and its arguments, note that the string is parsed using white space as the delimiter via the StringTokenizer class.
Stumbling into an IllegalThreadStateException The first pitfall relating to Runtime.exec() is the IllegalThreadStateException. The prevalent first test of an API is to code its most obvious methods. For example, to execute a process that is external to the Java VM, we use the exec() method. To see the value that the external process returns, we use the exitValue() method on the Process class. In our first example, we will attempt to execute the Java compiler (javac.exe):
Listing 4.1 BadExecJavac.java
import java.util.*; import java.io.*;
public class BadExecJavac { public static void main(String args[]) { try { Runtime rt = Runtime.getRuntime(); Process proc = rt.exec("javac"); int exitVal = proc.exitValue(); System.out.println("Process exitValue: " + exitVal); } catch (Throwable t) { t.printStackTrace(); } } }
A run of BadExecJavac produces:
E:\classes\com\javaworld\jpitfalls\article2>java BadExecJavac java.lang.IllegalThreadStateException: process has not exited at java.lang.Win32Process.exitValue(Native Method) at BadExecJavac.main(BadExecJavac.java:13)
If an external process has not yet completed, the exitValue() method will throw an IllegalThreadStateException; that's why this program failed. While the documentation states this fact, why can't this method wait until it can give a valid answer?
A more thorough look at the methods available in the Process class reveals a waitFor() method that does precisely that. In fact, waitFor() also returns the exit value, which means that you would not use exitValue() and waitFor() in conjunction with each other, but rather would choose one or the other. The only possible time you would use exitValue() instead of waitFor() would be when you don't want your program to block waiting on an external process that may never complete. Instead of using the waitFor() method, I would prefer passing a boolean parameter called waitFor into the exitValue() method to determine whether or not the current thread should wait. A boolean would be more beneficial because exitValue() is a more appropriate name for this method, and it isn't necessary for two methods to perform the same function under different conditions. Such simple condition discrimination is the domain of an input parameter.
Therefore, to avoid this trap, either catch the IllegalThreadStateException or wait for the process to complete.
Now, let's fix the problem in Listing 4.1 and wait for the process to complete. In Listing 4.2, the program again attempts to execute javac.exe and then waits for the external process to complete:
Listing 4.2 BadExecJavac2.java
import java.util.*; import java.io.*;
public class BadExecJavac2 { public static void main(String args[]) { try { Runtime rt = Runtime.getRuntime(); Process proc = rt.exec("javac"); int exitVal = proc.waitFor(); System.out.println("Process exitValue: " + exitVal); } catch (Throwable t) { t.printStackTrace(); } } }
Unfortunately, a run of BadExecJavac2 produces no output. The program hangs and never completes. Why does the javac process never complete?
Why Runtime.exec() hangs The JDK's Javadoc documentation provides the answer to this question:
Because some native platforms only provide limited buffer size for standard input and output streams, failure to promptly write the input stream or read the output stream of the subprocess may cause the subprocess to block, and even deadlock.
Is this just a case of programmers not reading the documentation, as implied in the oft-quoted advice: read the fine manual (RTFM)? The answer is partially yes. In this case, reading the Javadoc would get you halfway there; it explains that you need to handle the streams to your external process, but it does not tell you how.
Another variable is at play here, as is evident by the large number of programmer questions and misconceptions concerning this API in the newsgroups: though Runtime.exec() and the Process APIs seem extremely simple, that simplicity is deceiving because the simple, or obvious, use of the API is prone to error. The lesson here for the API designer is to reserve simple APIs for simple operations. Operations prone to complexities and platform-specific dependencies should reflect the domain accurately. It is possible for an abstraction to be carried too far. The JConfig library provides an example of a more complete API to handle file and process operations (see Resources below for more information).
Now, let's follow the JDK documentation and handle the output of the javac process. When you run javac without any arguments, it produces a set of usage statements that describe how to run the program and the meaning of all the available program options. Knowing that this is going to the stderr stream, you can easily write a program to exhaust that stream before waiting for the process to exit. Listing 4.3 completes that task. While this approach will work, it is not a good general solution. Thus, Listing 4.3's program is named MediocreExecJavac; it provides only a mediocre solution. A better solution would empty both the standard error stream and the standard output stream. And the best solution would empty these streams simultaneously (I'll demonstrate that later).
Listing 4.3 MediocreExecJavac.java
import java.util.*; import java.io.*;
public class MediocreExecJavac { public static void main(String args[]) { try { Runtime rt = Runtime.getRuntime(); Process proc = rt.exec("javac"); InputStream stderr = proc.getErrorStream(); InputStreamReader isr = new InputStreamReader(stderr); BufferedReader br = new BufferedReader(isr); String line = null; System.out.println(""); while ( (line = br.readLine()) != null) System.out.println(line); System.out.println(""); int exitVal = proc.waitFor(); System.out.println("Process exitValue: " + exitVal); } catch (Throwable t) { t.printStackTrace(); } } }
where includes: -g Generate all debugging info -g:none Generate no debugging info -g:{lines,vars,source} Generate only some debugging info -O Optimize; may hinder debugging or enlarge class files -nowarn Generate no warnings -verbose Output messages about what the compiler is doing -deprecation Output source locations where deprecated APIs are used -classpath Specify where to find user class files -sourcepath Specify where to find input source files -bootclasspath Override location of bootstrap class files -extdirs Override location of installed extensions -d Specify where to place generated class files -encoding Specify character encoding used by source files -target Generate class files for specific VM version
Process exitValue: 2
So, MediocreExecJavac works and produces an exit value of 2. Normally, an exit value of 0 indicates success; any nonzero value indicates an error. The meaning of these exit values depends on the particular operating system. A Win32 error with a value of 2 is a "file not found" error. That makes sense, since javac expects us to follow the program with the source code file to compile.
Thus, to circumvent the second pitfall -- hanging forever in Runtime.exec() -- if the program you launch produces output or expects input, ensure that you process the input and output streams.
Assuming a command is an executable program Under the Windows operating system, many new programmers stumble upon Runtime.exec() when trying to use it for nonexecutable commands like dir and copy. Subsequently, they run into Runtime.exec()'s third pitfall. Listing 4.4 demonstrates exactly that:
Listing 4.4 BadExecWinDir.java
import java.util.*; import java.io.*;
public class BadExecWinDir { public static void main(String args[]) { try { Runtime rt = Runtime.getRuntime(); Process proc = rt.exec("dir"); InputStream stdin = proc.getInputStream(); InputStreamReader isr = new InputStreamReader(stdin); BufferedReader br = new BufferedReader(isr); String line = null; System.out.println(""); int exitVal = proc.waitFor(); System.out.println("Process exitValue: " + exitVal); } catch (Throwable t) { t.printStackTrace(); } } }
A run of BadExecWinDir produces:
E:\classes\com\javaworld\jpitfalls\article2>java BadExecWinDir java.io.IOException: CreateProcess: dir error=2 at java.lang.Win32Process.create(Native Method) at java.lang.Win32Process.(Unknown Source) at java.lang.Runtime.execInternal(Native Method) at java.lang.Runtime.exec(Unknown Source) at java.lang.Runtime.exec(Unknown Source) at java.lang.Runtime.exec(Unknown Source) at java.lang.Runtime.exec(Unknown Source) at BadExecWinDir.main(BadExecWinDir.java:12)
As stated earlier, the error value of 2 means "file not found," which, in this case, means that the executable named dir.exe could not be found. That's because the directory command is part of the Windows command interpreter and not a separate executable. To run the Windows command interpreter, execute either command.com or cmd.exe, depending on the Windows operating system you use. Listing 4.5 runs a copy of the Windows command interpreter and then executes the user-supplied command (e.g., dir).
Listing 4.5 GoodWindowsExec.java
import java.util.*; import java.io.*;
class StreamGobbler extends Thread { InputStream is; String type;
// any output? StreamGobbler outputGobbler = new StreamGobbler(proc.getInputStream(), "OUTPUT");
// kick them off errorGobbler.start(); outputGobbler.start();
// any error??? int exitVal = proc.waitFor(); System.out.println("ExitValue: " + exitVal); } catch (Throwable t) { t.printStackTrace(); } } }
Running GoodWindowsExec with the dir command generates:
E:\classes\com\javaworld\jpitfalls\article2>java GoodWindowsExec "dir *.java" Execing cmd.exe /C dir *.java OUTPUT> Volume in drive E has no label. OUTPUT> Volume Serial Number is 5C5F-0CC9 OUTPUT> OUTPUT> Directory of E:\classes\com\javaworld\jpitfalls\article2 OUTPUT> OUTPUT>10/23/00 09:01p 805 BadExecBrowser.java OUTPUT>10/22/00 09:35a 770 BadExecBrowser1.java OUTPUT>10/24/00 08:45p 488 BadExecJavac.java OUTPUT>10/24/00 08:46p 519 BadExecJavac2.java OUTPUT>10/24/00 09:13p 930 BadExecWinDir.java OUTPUT>10/22/00 09:21a 2,282 BadURLPost.java OUTPUT>10/22/00 09:20a 2,273 BadURLPost1.java ... (some output omitted for brevity) OUTPUT>10/12/00 09:29p 151 SuperFrame.java OUTPUT>10/24/00 09:23p 1,814 TestExec.java OUTPUT>10/09/00 05:47p 23,543 TestStringReplace.java OUTPUT>10/12/00 08:55p 228 TopLevel.java OUTPUT> 22 File(s) 46,661 bytes OUTPUT> 19,678,420,992 bytes free ExitValue: 0
Running GoodWindowsExec with any associated document type will launch the application associated with that document type. For example, to launch Microsoft Word to display a Word document (i.e., one with a .doc extension), type:
>java GoodWindowsExec "yourdoc.doc"
Notice that GoodWindowsExec uses the os.name system property to determine which Windows operating system you are running -- and thus determine the appropriate command interpreter. After executing the command interpreter, handle the standard error and standard input streams with the StreamGobbler class. StreamGobbler empties any stream passed into it in a separate thread. The class uses a simple String type to denote the stream it empties when it prints the line just read to the console.
Thus, to avoid the third pitfall related to Runtime.exec(), do not assume that a command is an executable program; know whether you are executing a standalone executable or an interpreted command. At the end of this section, I will demonstrate a simple command-line tool that will help you with that analysis.
It is important to note that the method used to obtain a process's output stream is called getInputStream(). The thing to remember is that the API sees things from the perspective of the Java program and not the external process. Therefore, the external program's output is the Java program's input. And that logic carries over to the external program's input stream, which is an output stream to the Java program.
Runtime.exec() is not a command line One final pitfall to cover with Runtime.exec() is mistakenly assuming that exec() accepts any String that your command line (or shell) accepts. Runtime.exec() is much more limited and not cross-platform. This pitfall is caused by users attempting to use the exec() method to accept a single String as a command line would. The confusion may be due to the fact that command is the parameter name for the exec() method. Thus, the programmer incorrectly associates the parameter command with anything that he or she can type on a command line, instead of associating it with a single program and its arguments. In listing 4.6 below, a user tries to execute a command and redirect its output in one call to exec():
Listing 4.6 BadWinRedirect.java
import java.util.*; import java.io.*;
// StreamGobbler omitted for brevity
public class BadWinRedirect { public static void main(String args[]) { try { Runtime rt = Runtime.getRuntime(); Process proc = rt.exec("java jecho 'Hello World' > test.txt"); // any error message? StreamGobbler errorGobbler = new StreamGobbler(proc.getErrorStream(), "ERROR");
// any output? StreamGobbler outputGobbler = new StreamGobbler(proc.getInputStream(), "OUTPUT");
// kick them off errorGobbler.start(); outputGobbler.start();
// any error??? int exitVal = proc.waitFor(); System.out.println("ExitValue: " + exitVal); } catch (Throwable t) { t.printStackTrace(); } } }
The program BadWinRedirect attempted to redirect the output of an echo program's simple Java version into the file test.txt. However, we find that the file test.txt does not exist. The jecho program simply takes its command-line arguments and writes them to the standard output stream. (You will find the source for jecho in the source code available for download in Resources.) In Listing 4.6, the user assumed that you could redirect standard output into a file just as you could on a DOS command line. Nevertheless, you do not redirect the output through this approach. The incorrect assumption here is that the exec() method acts like a shell interpreter; it does not. Instead, exec() executes a single executable (a program or script). If you want to process the stream to either redirect it or pipe it into another program, you must do so programmatically, using the java.io package. Listing 4.7 properly redirects the standard output stream of the jecho process into a file.
After running GoodWinRedirect, test.txt does exist. The solution to the pitfall was to simply control the redirection by handling the external process's standard output stream separately from the Runtime.exec() method. We create a separate OutputStream, read in the filename to which we redirect the output, open the file, and write the output that we receive from the spawned process's standard output to the file. Listing 4.7 completes that task by adding a new constructor to our StreamGobbler class. The new constructor takes three arguments: the input stream to gobble, the type String that labels the stream we are gobbling, and the output stream to which we redirect the input. This new version of StreamGobbler does not break any of the code in which it was previously used, as we have not changed the existing public API -- we only extended it.
Since the argument to Runtime.exec() is dependent on the operating system, the proper commands to use will vary from one OS to another. So, before finalizing arguments to Runtime.exec() and writing the code, quickly test the arguments. Listing 4.8 is a simple command-line utility that allows you to do just that.
Here's a useful exercise: try to modify TestExec to redirect the standard input or standard output to a file. When executing the javac compiler on Windows 95 or Windows 98, that would solve the problem of error messages scrolling off the top of the limited command-line buffer.
Listing 4.8 TestExec.java
import java.util.*; import java.io.*;
// class StreamGobbler omitted for brevity
public class TestExec { public static void main(String args[]) { if (args.length < 1) { System.out.println("USAGE: java TestExec \"cmd\""); System.exit(1); }
// any error message? StreamGobbler errorGobbler = new StreamGobbler(proc.getErrorStream(), "ERR");
// any output? StreamGobbler outputGobbler = new StreamGobbler(proc.getInputStream(), "OUT");
// kick them off errorGobbler.start(); outputGobbler.start();
// any error??? int exitVal = proc.waitFor(); System.out.println("ExitValue: " + exitVal); } catch (Throwable t) { t.printStackTrace(); } } }
Running TestExec to launch the Netscape browser and load the Java help documentation produces:
E:\classes\com\javaworld\jpitfalls\article2>java TestExec "e:\java\docs\index.html" java.io.IOException: CreateProcess: e:\java\docs\index.html error=193 at java.lang.Win32Process.create(Native Method) at java.lang.Win32Process.(Unknown Source) at java.lang.Runtime.execInternal(Native Method) at java.lang.Runtime.exec(Unknown Source) at java.lang.Runtime.exec(Unknown Source) at java.lang.Runtime.exec(Unknown Source) at java.lang.Runtime.exec(Unknown Source) at TestExec.main(TestExec.java:45)
Our first test failed with an error of 193. The Win32 error for value 193 is "not a valid Win32 application." This error tells us that no path to an associated application (e.g., Netscape) exists, and that the process cannot run an HTML file without an associated application.
Therefore, we try the test again, this time giving it a full path to Netscape. (Alternately, we could add Netscape to our PATH environment variable.) A second run of TestExec produces:
This worked! The Netscape browser launches, and it then loads the Java help documentation.
One additional improvement to TestExec would include a command-line switch to accept input from standard input. You would then use the Process.getOutputStream() method to pass the input to the spawned external program.
To sum up, follow these rules of thumb to avoid the pitfalls in Runtime.exec():
You cannot obtain an exit status from an external process until it has exited
You must immediately handle the input, output, and error streams from your spawned external process
You must use Runtime.exec() to execute programs
You cannot use Runtime.exec() like a command line
Correction to Pitfall 3 In the discussion of Pitfall 3 ("Don't mix floats and doubles when generating text or XML messages") in my last column, I incorrectly stated that the different string representation of a decimal number after casting it from a float to a double was a bug. While this is a pitfall, its cause is not a bug, but the fact that the decimal numbers in question -- 100.28 and 91.09 -- do not represent precisely in binary. I'd like to thank Thomas Okken and the others who straightened me out. If you enjoy discussing the finer points of numerical methods, you can email Thomas.
The combination of forgetting my numerical methods class, the numerous bug reports on the bug parade, and the automatic rounding of floats and doubles when printing (but not after casting a float to a double) threw me. I apologize for confusing anyone who read the article, especially to new Java programmers. I present two better solutions to the problem:
The first possible solution is to always specify the desired rounding explicitly with NumberFormat. In my case, I use the float and double to represent dollars and cents; therefore, I need only two significant digits. Listing C3.1 demonstrates how to use the NumberFormat class to specify a maximum of two fraction digits.
Listing C3.1 FormatNumbers.java
import java.text.*;
public class FormatNumbers { public static void main(String [] args) { try { NumberFormat fmt = NumberFormat.getInstance(); fmt.setMaximumFractionDigits(2); float f = 100.28f; System.out.println("As a float : " + f); double d = f; System.out.println("Cast to a double : " + d); System.out.println("Using NumberFormat: " + fmt.format(d)); } catch (Throwable t) { t.printStackTrace(); } } }
When we run the FormatNumbers program, it produces:
E:\classes\com\javaworld\jpitfalls\article2>java FormatNumbers As a float : 100.28 Cast to a double : 100.27999877929688 Using NumberFormat: 100.28
As you can see -- regardless of whether we cast the float to a double -- when we specify the number of digits we want, it properly rounds to that precision -- even if the number is infinitely repeating in binary. To circumvent this pitfall, control the formatting of your doubles and floats when converting to a String.
A second, simpler solution would be to not use a float to represent cents. Integers (number of pennies) can represent cents, with a legal range of 0 to 99. You can check the range in the mutator method.
Next time In my next column, I'll present another pitfall from java.lang, as well as two traps hiding in the java.net and the Swing packages. If you know of any Java pitfalls that have wasted your time and caused you frustration, please email them to me so we can save others the same fate.
About the author Michael C. Daconta is the director of Web and technology services for McDonald Bradley, where he conducts training seminars and develops advanced systems with Java, JavaScript, and XML. Over the past 15 years, Daconta has held every major development position, including chief scientist, technical director, chief developer, team leader, systems analyst, and programmer. He is a Sun-certified Java programmer and coauthor of Java Pitfalls (John Wiley & Sons, 2000), Java 2 and JavaScript for C and C++ Programmers (John Wiley & Sons, 1999), and XML Development with Java 2 (Sams Publishing, 2000). In addition, he is the author of C++ Pointers and Dynamic Memory
The Case for the Surrogate Key A simple key to the flexible enterprise database There is a simple, inexpensive, uniform element of database design that you are likely avoiding in favor of a complex, costly, and inconsistent one. This element is the surrogate or substitute primary key. It seems that designers avoid these independent keys like the plague. Instead, all but the most basic business entities are given keys made up of some series of attributes, borrowed keys, and sequence numbers . It was only after experiencing many problems at the hand of intelligent keys that I, too, became a believer in the flexibility and stability afforded by the substitute key. In this article I'll illustrate why the surrogate primary key is a critically important ingredient in designing stable, flexible, and well-performing enterprise databases. I will define the issues, analyze them to arrive at a set of principles of primary key design, identify the criteria for defining a surrogate key on a table, suggest a standard for implementation, and finally, note some benefits our shop (J. B. Hunt Transport Inc.) enjoys from consistent application of a surrogate primary key. THE ISSUES The first issue is the function of the primary key. What exactly is the real purpose of the primary key? Because form follows function, defining the role of the primary key will settle much debate about its format, and it will help us determine some principles with which we can resolve the remaining issues. Another issue is the distinction between the logical and physical keys. Can these keys be the same? For example, a customer identifier and date may be used to check for a duplicate order before entering it into a database. A sequence number may be added to distinguish line items one from another. But once the order and line items are entered into the database, should these data elements form the primary keys for these tables? The meaningfulness of the primary key is another issue I'll discuss. How much information should the primary key hold? For example, a legacy system in our shop gave to each transport accident a primary key formed by combining the year with an accident sequence number and a type code. Another application assigned order numbers within ranges based on order type. Are these good choices for the primary key? The final issue I will address is the source of the primary key. Can it be provided from outside the system or by a system user? Examples are Social Security numbers to identify people; user-generated pnemonic codes to identify customers and accounts; and catalog, part, or transaction numbers generated by external agencies. Why not use these identifiers as primary keys? ANALYSIS Proper primary key design recognizes that there are two unique keys to be considered. Database designers make a critical mistake when they fail to recognize the distinction between these keys. The logical unique key distinguishes entity occurrences from others to avoid duplicates before entry into the database. That is why this key is typically composed of attributes and relationships that the business can recognize as distinguishing occurrences logically from others. The primary key, however, is a physical design element that uniquely identifies rows after they are in the database. It is used in creating foreign keys in other tables in order to establish the logical relationships in the data model. In fact, it is best to consider a primary key's primary purpose to be establishing relationships in the database! The primary key's role as a physical row identifier has important implications. First, it requires that the primary key be physically unique in the primary key table. Tables are sometimes created with primary keys that logically identify occurrences but may appear more than once in the primary key table. This repetition usually betrays a failure to define properly either the criteria for new occurrences of the entity or the real physical purpose of the table. For example, a table may store work order types and identify them with a number or code. But the identifier may appear on more than one "inactive" row in the primary key table with perhaps one occurrence in an "active" status. In this case, the meaning of the work order type becomes unclear. Are all rows with the same key related to the original occurrence? Or are they truly different work order types that have passed their identifiers to newer occurrences? Alternatively, several rows sharing a primary key value may be distinguished by effective and end date ranges. In this case, the table is really acting as an audit of changes to the logical occurrence. But what table really represents the business entity itself? Second, the primary key must never be null. This must be true even if any of the elements of the logical unique key are unknown. Like nonunique primary keys, null primary keys usually signal a failure to define clearly what the row represents. If we create an ORDER but fail to give it an order number until it is shipped, does the row represent the ORDER or the SHIPMENT? If we create an EMPLOYEE APPLICANT but do not assign a key until the application is received, are we equating the APPLICANT with the APPLICATION? What happens if we get a second APPLICATION from the same APPLICANT? Third, the primary key must be absolutely stable. It must be protected in all cases from change. This does not mean protected from likely change; rather, it must be guaranteed not to be allowed to change. This third principle is always violated when an "intelligent" key is used as the primary key. In the first example, the relationships and attributes that logically identify the order and line item are also used as the primary key (see Figure 1). One problem with these is that they can change. The order's customer may be incorrectly entered, for example. If we discover the error midway through processing the order, how do we correct it? There are two unpleasant options: We can either create a brand new order and cancel the original, or we can change the primary key value of the order. Suppose we choose the first option and create another order. Is this a true picture of business reality? That is, do we really have another order? Do we really want to reprocess the order under a new identifier? 1A If any attribute making up the primary key of the ORDER or ORDER LINE ITEM changes, all foreign key references to these tables (TABLE A, TABLE B, and TABLE C) must also be updated and committed together.
1B Here the ORDER and ORDER LINE ITEM tables have surrogate primary keys different than the unique keys. All foreign key references to them (TABLE A, TABLE B, and TABLE C) are insulated from changes to unique key attributes.
Figure 1. Eliminating the Effect of Change in Key Value.
The second option is to change the value of the order's primary key. In this case, we must also change every foreign key reference to the order within the same commit unit! Each new reference must also be included in this update. Would it not be more correct and efficient simply to change the order's customer and continue processing the order? A surrogate key independent of all the order's attributes or relationships allows us to correct the order and maintain the information already contained in the database easily. Another problem illustrated here is that of concatenated primary keys. Because the order's primary key in our example has been migrated into the primary key of the line item, all references to the order line item must also change with a change in the order's key. The problem grows as we chain together more keys in the database. Migration of primary keys in this fashion has terrible implications for database flexibility. These examples illustrate how primary keys must be insulated from changes in value and so should not comprise attributes, relationships, or primary keys migrated from other tables. But even if its value is truly stable, the primary key must never be the same as the logical unique key because the set of attributes and relationships that make an entity unique may itself change. Associative entities provide a good example of this (see Figure 2). An associative entity contains attributes about a many-to-many relationship between two or more occurrences. Because the entity is defined by its relationships, we are tempted to use the foreign key columns in the primary key. For example, our company has a PART OFFERING associative entity, which stores pricing and configuration information for a PART as it is sold or offered by a VENDOR. A PART OFFERING is unique on the PART id, VENDOR id, and offering date. Why not use these columns as the primary key? 2A Here the PART OFFERING associative entity uses the logical unique key as the primary key. A change requiring PART OFFERINGs to be unique additionally on TIME requires changes to all related foreign key structures (TABLE A and TABLE B).
2B The PART OFFERING has a surrogate key different from the logical unique key. The new requirement to make PART OFFERING unique on an additional TIME attribute does not impact any foreign key structures (TABLE A and TABLE B).
Figure 2. Eliminating the Effect of Change in Key Structure.
The reason is that over time, the business may wish to capture PART OFFERINGs at a finer level of detail. We may begin to track multiple PART OFFERINGs for fuel offered by the same vendor at more than one price in a day. If we had used the unique key as the primary key, we would need to modify all programs and tables that reference unique PART OFFERINGs to include the offering time. A more stable design would give the PART OFFERING a surrogate key. The new business requirement would require only changes to the unique key index on the PART OFFERING table and any programs that insert or update its rows. The programs and other tables referencing the PART OFFERING by the surrogate key would be unaffected. The third issue I raised involves primary keys that contain attributes unrelated to the logical unique key. This type of key appears in many forms and is subject to the same problems I addressed above. Additionally, these keys are especially inflexible to expansion of the definition or usage of data. New subtypes of orders or accidents, for example, are typically handled by assigning different ranges or formats of primary key values. As these applications increase, smaller key ranges cause rollover and archiving issues while applications are forced to come up with imaginative new identification schemes just to insert a row in the database. Another example of this kind of key occurs when a key value implicitly represents a hierarchy among rows in a table. This type of key is inflexible to additional levels and limits the number of occurrences at any given level. For example, standard commodity codes used across the transportation industry are assigned key values whose format indicates an arbitrary level in a hierarchy of commodity classifications. A broad commodity classification is given a key of 100000; the classification level immediately beneath it has keys of the form 101000, 102000, and so on; and the next level keys of the form 101010, 101020. A better design would give each occurrence a surrogate primary key; child occurrences would have a foreign key back to the parent. In this way, classifications can have a varying and unlimited number of levels and occurrences. Finally, primary key values must be internally assigned. The first reason is, again, due to stability. Social Security numbers may be miskeyed, unknown, or changed; business users may invent new coding schemes to identify customers; and outside agencies may reformat or reuse identifiers at their own will. Another reason the values must be assigned internally is that we may define entities differently than the outside agencies that provide our data. For example, a vendor that sells us uniforms uses a different ITEM number to identify each different size of uniform. In our database, however, we create one ITEM with many sizes. This standard definition allows us to draw consistent information out of the database independent of vendor numbering schemes. Using the vendor's assigned ITEM number would cause us to create ITEMs based on each vendor's definition and not ours. The best way to avoid the pitfalls indicated by these illustrations is to choose a primary key that is logically meaningless; this is the surrogate primary key. Doing this means, above all, that the primary key must be owned by the data administrator. Intelligent, problematic primary keys will be assigned when application developers or users begin to take ownership of the primary key. When this happens, the real purpose of the primary key is lost; it becomes a "good idea" number that tells a story about the row it identifies. The primary key is not a "good idea" number; its purpose is to physically identify a row to the database. To summarize, the primary key must be: Unique Not null Stable Different than the logical unique key Internally assigned Logically meaningless. THE CRITERIA Does every table require a surrogate primary key? The answer is no. The prerequisites derive from the definition of the primary key. Because the primary key identifies a row after it is in the database, it follows that a table requires a primary key if its rows will be referenced individually by other tables after entry into the database. There are two alternative views of this. The logical view asks: does the table participate in more than one relationship from the logical data model? For example, in a human resources database, an EMPLOYEE table will likely participate in more than one relationship; we may relate the EMPLOYEE to BENEFITs, POSITIONs, LOCATIONs, EVALUATIONs, and many other entities in our data model. For this reason, the EMPLOYEE gets a surrogate key (see Figure 3). But now suppose we need a table to store the EMPLOYEE DEPENDENTs. We must determine whether or not the DEPENDENT participates in more than one relationship from the data model. If we care to relate the DEPENDENT only to the EMPLOYEE, then the DEPENDENT table represents a repeating attribute of the EMPLOYEE entity. It does not require a surrogate key, but it may have a unique key consisting of the EMPLOYEE key and some combination of attributes of the DEPENDENT. If, however, we also wish to relate DEPENDENTs to BENEFITs, then the DEPENDENT participates in a second relationship, becomes an entity in the logical data model, and gets a surrogate key. 3A The DEPENDENT is related only to the EMPLOYEE, and has a unique key but no surrogate key.
3B The DEPENDENT has its own relationships (TABLE A and TABLE B) and gets a surrogate key.
Figure 3. The Data Model in Primary Key Assignment.
The physical view asks: Do we need to reference the rows individually for physical processing reasons? For example, we may wish to trigger certain DEPENDENTs to be processed through some application. Rather than reading all rows in the table looking for a few process flags, we may give the DEPENDENT a surrogate key and store the values of the "flagged" DEPENDENTs in a trigger table. After the DEPENDENT is processed, the row representing the DEPENDENT in the trigger table is deleted. This may be simpler than storing the series of columns that reference a single DEPENDENT. A SUGGESTED STANDARD Our shop has enjoyed success with the implementation of a simple primary key standard containing two critical elements. First, all our primary keys are sequentially assigned positive integers. In DB2, we use a large INTEGER column. This allows us to store more than 2.1 billion occurrences of any entity in our model. We can create an occurrence every second of every day for 68 years and still not run out of values. Certainly few business entities are created this frequently. Second, all primary keys are assigned through an enterprise module that receives a logical table name, obtains an exclusive lock on a row containing that table's next primary key value, increments the value, and passes it back to the caller. The primary key is assigned by logical table because we sometimes split a logical table into two or more physical tables for performance. The rows that form the one logical row share their primary key value. THE BENEFITS The consistent format of primary keys throughout our database provides benefits in addition to flexibility. One is in the implementation of optional relationships (see Figure 4). For example, we create INVOICE ITEMs but may not relate them to an INVOICE until perhaps several days later. The INVOICE foreign key in the INVOICE ITEM table thus needs to be logically null. Physically, however, a zero or dummy INVOICE value would heavily skew the foreign key index. Instead, the null foreign key receives a value equal to the row's own primary key multiplied by negative one. This distributes the foreign key index values evenly across all rows, whether or not the foreign key relationship exists. 4 The INVOICE ITEM has an optional relationship to the INVOICE. ITEMs without a related INVOICE receive a foreign key value equal to their primary key multiplied by -1. The index over the INVOICE foreign key is evenly distributed.
Figure 4. Handling Absent Foreign Keys.
A second benefit is that "Exclusive OR" relationships can be handled with one well-indexed column (see Figure 5). An "Exclusive OR" relationship exists when an entity occurrence may relate to at most one of a number of possible occurrences. A given INVOICE ITEM, for example, may pertain to one of any number of different possible entities in our model; we may invoice SHIPMENTs, STOPs, ACTIVITIES, or others. Different primary key domains would require a foreign key for each possible relationship, all but one of which would contain a value. Instead, we use a single indexed foreign key column. A second column names the table referenced by the foreign key. 5 The INVOICE ITEM can bill a SHIPMENT, STOP, or ACTIVITY, exclusively. A single foreign key handles all relationships. A second column names the table containing the primary key value.
Figure 5. Handlign Exclusive or Foreign Keys.
Poor primary key design is perhaps the most repeated and damaging flaw in database design. We would do well to remember that a smart key is a dumb thing to do, while a dumb key is a smart thing to do. Mike Lonigro is the senior data administrator responsible for all conceptual, logical, and physical database design at J. B. Hunt Transport Inc., the nation's largest publicly held truckload carrier. J.B. Hunt Transport runs on DB2 for OS/390 at more than 3 million transactions per day. Previously, Lonigro was a consultant with Price Waterhouse MCS in the Petroleum Special Practice Unit. You can reach him via email at lonigro@mail.jbhunt.com.
search - home - archives - contacts - site index
Copyright 1997 Miller Freeman Inc. All Rights Reserved Redistribution without permission is prohibited.
