by Jennifer Marsh

Created on: November 18, 2010

Most database engines have the ability to contain several million records. The more records contained in the database, the more time the engine needs to search through all the records for your chosen information. For instance, if you want all customers with a last name of “Smith,” the database server must find these customers among millions of records. It’s conceivable that this can take a long time to complete. A primary key helps sort these records, so your database server is faster and less memory and processor resources are required to complete the task.

What is a Primary Key?

A primary key is a unique field, which means no other record in the table has the value in the primary key column. This helps identify your database table record amongst all other records in your tables. Secondly, the primary key is an index. An index is a type of sorting feature used in databases to increase performance for your table queries. The database sorts your records based on the indexes, which include the primary key.  A primary key can turn the time for a query from several minutes to only a few milliseconds. This type of performance is an imperative part of your applications. Slow websites or employee desktop applications can cripple a business.

The Primary and Foreign Key Relationship

The primary key is a unique field in a database table, so it is used when “linking” tables. For instance, a customer number is unique to the customer, so it can be used as a primary key. The primary key is used to connect to other tables such as an “orders” table. The primary key connects to the foreign key in the customer’s order table. This type of connection gives the database developer the ability to link tables across the database, which is a critical part of creating queries.

Primary Keys and Queries

Database developers use primary keys to query records and find a single record in a table. Using the customer table example, you can query the primary key and find the one customer that matches. This is how you retrieve customer information when they log in to a website. It is also how an employee does a search for a customer during a customer service phone call. The representative searches for your customer number, which is the primary key on the customer table.

Primary keys are important for any table design. You can incorporate primary keys in SQL Server, MySQL and Oracle databases, which are the common database engines used in web and desktop applications.

Learn more about this author, Jennifer Marsh.
Click here to send this author comments or questions.