why does great care need to be taken when selecting a primary key?

Question

and also, what examples of a good key selection and bad key selections could you name?

Answer 1

A primary key should be unqiue.. usually with mysql you may want to use autoincrement as the primary key. and in mssql you can use identity.



a bad example of a primary key would be make first name as the primary key..

Answer 2

Above guy is just right..



Basic idea behind the primary key is



Each database table must have a field or a combination of fields that holds a value that uniquely identifies each record.



For example, a customer number would uniquely identify each customer. This field or fields are called the primary key of the table. The primary key serves several purposes in a RDBMS. Because the value is unique, it ensures there are no duplicate records in the database. The primary key is also used to establish table relationships and, thereby, connect the data in related records held in different tables. Records are stored in order by the primary key.



In order to be a primary key, the field must never be null. Null means there is a missing or unknown value. A null value is not the same as a zero or a blank. In a numeric field, a zero may be a real value. Another characteristic of a primary key field is that the values in that field are rarely (ideally never) changed.



In trying to identify what the primary key should be for a table, first look for a single field that will hold a value that is unique for each record. For example, in a table holding information on orders placed with a company, there would be a field with a unique number for each order.



To summarize, the characteristics of a primary key field or fields are that its values:



* uniquely identify each record (no duplicate values);

* are never null;

* rarely (if ever) change;

* and the key includes as few fields as possible.





Primary key are very important factor while doing query tuning/performance enhancement issues with applications.







Hope this helps

Cheers:)

Answer 3

It is usually best to make the primary key an auto-incrementing integer. There are a few reasons. First, it guarantees that the key will always be unique. Second, it works great as a foreign key when establishing relationships between tables. Third, integers columns are more efficient in joins than character strings, making queries run faster.



You can use other columns for uniquely identifying a row. For example, you may want to use the SSN to uniquely identify rows in an Employee table. However, it would be more advisable to put a unique index on that column and create an Auto-Inc PK called EmployeeID or something similar. You can use the SSN key to select, update or delete a unique record, but you should continue to use the EmployeeID in query joins. One exception to this rule is when using short character strings as "codes" to identify something. I usually reserve this to things that have widely used standards. For example, a 3 character code can be used as the PK in a Currency table, e.g. USD for the US Dollar.



Don't use any data that may need to be changed. For example, don't use an email address as a primary key. While it may be unique, if it changes you may run into issues when updating foreign keys in related tables.



Avoid using multiple columns to establish primary keys. For example don't use the first and last name of a person. One issue with that is, of course, it may not be as unique as it first seems. Second, it will require you to create multiple-columns as foreign keys in related tables. This is a waste of space and will reduce performance on queries that join the tables. One exception to this rule is for "linking" tables that are used to establish many-to-many relationships between two or more tables. In that case the PK of the linking table will usually be the combination of foreign keys from the other tables.