Choosing a Primary Key Natural or Surrogate This article overviews strategies for assigning primary. In particular, it. Some people will tell you that you should always. These people invariably prove to be wrong. The reality is that natural and. In other words, you need. This. article discusses 1. Common Key. Lets start by describing some common terminology pertaining to. These terms are Key. A. key is one or more data attributes that uniquely identify an entity. In a physical database a. Composite key.  A key that is composed of two or. Natural key.  A key that is formed of attributes. U. S. citizens are issued a Social Security Number SSN. SSN could be used as a natural key, assuming privacy laws allow it, for a. Person entity assuming the scope of your organization is limited to the U. S. Surrogate key. A key with no business meaning. Candidate key.  An entity type in a logical data model will have zero or more. LDMs, so. theres no hard and fast rules. For example, if we only interact with American citizens then. SSN is one candidate key for the Person entity type and the combination. Both of these. keys are called candidate keys because they are candidates to be chosen as the primary. Primary key.  The preferred key for an entity type. Alternate key. Also known as a secondary key, is. Foreign key. One or more attributes in an entity type. Figure 1 presents a. PDM for a physical address using the. In Figure 1 the Customer. Customer. Number column as. Social. Security. Number. as an alternate key. This indicates. The Customer. Has. I was able to drop the primary key, alter the column data types, and recreate the primary key with this code using System. Collections. Specialized foreach Table. What isare primary keys what is composite primary key what are foreign keys what are tuplesAddress table. Customer. Number and Address. ID. A foreign key is one or more attributes in an entity type that represents. Foreign keys are used to maintain relationships between rows. A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to. Database Primary and Foreign Keys Primary and foreign keys are the most basic components on which relational database theory is based. Primary keys enforce entity. Task Topic Describes how to create a primary key. Create Primary Keys Describes how to delete a primary key. Delete Primary Keys Describes how to modify a primary key. Windows Presentation Foundation WPF provides a rich environment for creating applications. However, when you have a substantial investment in Windows Forms code, it. For example, the relationships between rows in the Customer. Has. Address table and the Customer table is maintained by the Customer. Number column within the Customer. Has. Address table. The. Customer. Number. Customer. Has. Address as well as the foreign key to the Customer. Similarly, the Address. ID column is part of the primary key of Customer. Has. Address as well as a foreign key to the Address. Address. A simple PDM modeling Customer and Address. There are two strategies for assigning keys to tables Natural keys. A natural key is one or more existing data. For the Customer table there was two candidate keys, in this case. Customer. Number. Social. Security. Number. Surrogate key. Introduce a new column, called a surrogate key. An example of which is the Address. ID. column of the Address table in. Addresses dont have an. Address table to form a key for itself you might be able to get. Street and Zip. Code depending on. The advantage of natural keys is that they exist. However, the disadvantage of natural keys is that because they. For. example, if your users decide to make Customer. Number. alphanumeric instead of numeric then in addition to updating the schema for the Customer. Customer. Number is used as a foreign key. There are several advantages to surrogate keys. First, they. For example, if the Customer. Customer table itself Customer. Number. in this case would just be a non key column of the table. Of course, if you needed to make a similar change to your surrogate key. Second. a common key strategy across most, or better yet all, tables can reduce the. TCO of the systems that you build. The fundamental. The implication is that. The fundamental issue is that keys. The implication is that you generally want to. Having. said that, I have a tendency to use natural keys for lookupreference tables. I suspect that the key values wont change any time soon, as I. Fundamentally, there is no clear answer as to whether or not you. There are several common options for implementing surrogate keys Key values assigned by the database. Most of the leading database vendors companies such as Oracle. Sybase, and Informix implement a surrogate key strategy called. The basic. idea is that they maintain a counter within the database server, writing the. Every time a row is created the counter is incremented and. The implementation strategies vary from vendor to vendor. MAX 1. A common strategy is to use an integer column, start the value for. SQL MAX function. Although this approach is simple it suffers from performance problems. Universally unique identifiers UUIDs. UUIDs are 1. 28 bit values that are created from a hash of the ID of. Ethernet card, or an equivalent software representation, and the. The algorithm for doing this is defined by the Open Software. Foundation www. opengroup. Globally unique identifiers GUIDs. Microsoft standard that extend UUIDs, following the same strategy if an. Ethernet card exists and if not then they hash a software ID and the current. High low strategy. The basic idea is that your key value, often called a persistent. POID or simply an object identified OID, is in two. A unique HIGH value that you obtain from a defined source and. N digit LOW value that your application assigns itself. Each time that a HIGH value is obtained the LOW value will be. For example, if. the application that youre running requests a value for HIGH it will be. Assuming. that N, the number of digits for LOW, is four then all persistent object. At this point a new value for HIGH is obtained, LOW is reset to zero. If. another application requests a value for HIGH immediately after you it will. OIDs that will be assigned to objects that. As you can see, as long as HIGH is unique then all POID values will. The fundamental issue is that keys are a significant. The. implication is that you want to avoid keys with business meaning because. However. at the same time you need to remember that some data is commonly. American employees via their Social Security Number SSN. In these cases you may want to use the natural key instead of a. UUID or POID. How can you be effective at assigning keys Consider the following tips. Avoid smart keys. A smart key is one. For example the first two digits of an U. S. zip code indicate. The. first problem with smart keys is that have business meaning. The second problem is that their use often becomes convoluted over. For example some large. California has zip codes beginning with 9. Third, they often increase the chance that the strategy will need to. Considering that zip codes are nine digits in length the. Consider assigning natural keys for simple look. A look up table is one that is used to relate codes. For. example, you might have a look up table listing color codes to the names of. For example the code. Tulip Yellow.  Simple. Natural keys dont always work for look up. Another example of a look up table is one that contains a row. North America. For example there would be a row for California, a US state, and for. Ontario, a Canadian province. The. Northwest. Territories, a territory of Canada, was split into Nunavut and Northwest. Territories.  A valid natural. CA for California and ON for Ontario. Unfortunately this approach doesnt work because Canadian. NW, for the two territories. Your applications must still support natural key. If you choose. to take a surrogate key approach to your database design you mustnt. For example, your Customer table may have a CustomerPOID. CustomerNumber column. SocialSecurityNumber column. How To Access Downloads On Windows Vista Administrator Account there. You would likely need to support searches based on both the customer. Searching is discussed in detail in. Best Practices. for Retrieving. Objects from a Relational Database. Dont naturalize surrogate keys.