Primary & Foreign Keys Explained

Primary and foreign keys are the main structures that connect database tables. Primary keys are unique values in a table that identify a specific record such as a customer number. Foreign keys are records in a separate table that is used to connect to the primary key. An example of a primary and foreign key structure is a customer number in a customer table connected to the foreign key customer number in an orders table. These connections are used across hundreds of tables to create e-commerce, content and support databases.

Significance

The most significant reason for primary and foreign keys is the identification of unique records in each table in the database. Primary keys are also indexed in the database, which makes it faster for the database server to retrieve the requested record. A primary key can also have several foreign keys established. For instance, a primary key in a customer table might have a foreign key in the orders and payments tables.

Identification

Primary keys are identified in database software with the "PK" prefix on the name or using the user interface. On the user interface, open a table and view the columns. The primary and foreign key are identified by a small key icon. The best way for a database administrator to find all foreign keys associated with a primary key is using a database diagram. Programs like SQL Server Management Console and Microsoft Access have diagrammed images that display these configurations.

Function

Primary and foreign keys are used to connect tables. A primary key is a unique identification number for a record. Foreign keys are not always unique. For instance, a customer can have several orders, so several records are in the table. The function of the keys connects these rows, so users are able to read the data pertaining to specific records.

Effects

One effect of using primary and foreign keys is connecting tables, but it also increases the speed of database functionality. A primary key is indexed, which sorts the records, making them faster when queried. Records searched on the primary key are also faster than searching on other fields in the table. This action improves response time of the database server and increases performance of the software.

Considerations

Only one primary key can be given to a table. The administrator can create a composite primary key, which is a primary key that makes up more than one column in the table. This is useful for tables that hold several records and avoids the possibility of the same primary key being entered. Foreign keys are located on several tables in the database, and they do not need to be unique.

References

• Database Dev: Primary and Foreign Key Constraints

Photo Credit

Image by Flickr.com, courtesy of gnizr