Apress - Pro SQL Server 2008 Relational Database Design and Implementation (2008)04 | 18 CHAPTER 1 INTRODUCTION TO DATABASE CONCEPTS The choice of primary key is largely a matter of convenience and what is easiest to use. We ll discuss primary keys later in this chapter in the context of relationships. The important thing to remember is that when you have values that should exist only once in the database you need to protect against duplicates. Choosing Keys While keys can consist of any number of columns it is best to try to limit the number of columns in a key as much as possible. For example you may have a Book table with the columns Publisher_Name Publisher_City ISBN_Number Book_Name and Edition. From these attributes the following three keys might be defined Publisher_Name Book_Name Edition A publisher will likely publish more than one book. Also it is safe to assume that book names are not unique across all books. However it is probably true that the same publisher will not publish two books with the same title and the same edition at least we assume that this is true . ISBN_Number The ISBN number is the unique identification number assigned to a book when it is published. Publisher_City ISBN_Number Because ISBN_Number is unique it follows that Publisher_City and ISBN_Number combined is also unique. The choice of Publisher_Name Book_Name as a composite candidate key seems valid but the Publisher_City ISBN_Number key requires more thought. The implication of this key is that in every city ISBN_Number can be used again a conclusion that is obviously not appropriate. This is a common problem with composite keys which are often not thought out properly. In this case you might choose ISBN_Number as the PK and Publisher_Name Book_Name as the AK. Note It is important to not confuse unique indexes with keys. There may be valid performance-based reasons to implement the Publisher_City ISBN_Number index in your SQL Server database. However this would not be identified as a key of a table. In Chapter 6 we ll discuss implementing keys and in Chapter 8 we
