A primary key is used as a unique identifier to quickly parse data within the table. A table cannot have more than one primary key. A primary key might use one or more fields already present in the underlying data model, or a specific extra field can be created to be the primary key. The primary key concept is critical to an efficient relational database. Without the primary key and closely related foreign key concepts, relational databases would not work.
In fact, since a table can easily contain thousands of records including duplicates , a primary key is necessary to ensure that a table record can always be uniquely identified.
All keys that come from real-world observables and attributes are called natural primary keys, as opposed to surrogate primary keys that are, instead, arbitrarily assigned to each record. Almost all individuals deal with natural primary keys frequently but unknowingly in everyday life.
For example, students are routinely assigned unique identification ID numbers, and all U. Street addresses or driver license numbers are examples of primary keys used to uniquely identify respectively locations or cars. As another example, a database must hold all of the data stored by a commercial bank. To uniquely identify customers, a column or combination of columns is selected to guarantee that two customers never have the same unique value.
Thus, certain columns are immediately eliminated, e. A good primary key candidate is the column that is designated to hold Social Security numbers. The next logical option is to use a combination of columns, such as adding the surname to the date of birth to the email address, resulting in a long and cumbersome primary key.
Then, the database automatically generates a unique number each time a customer is added, guaranteeing unique identification. As this key is created, the column is designated as the primary key within the SQL script that creates the table, and all null values are automatically rejected. For example, a customer may be asked to provide his surname when conducting a bank query. A common surname such as Smith query is likely to return multiple results. When querying data, utilizing the primary key uniqueness feature guarantees one result.
By: Justin Stoltzfus Contributor, Reviewer. By: Satish Balakrishnan. Dictionary Dictionary Term of the Day. Natural Language Processing. Techopedia Terms. If we try to insert a new record that uses an existing value in the primary key, the record will be rejected. We can see this in the following image:. Primary keys also help enforce referential integrity, another important part of data consistency.
In other words, a record with a matching id value must exist in the table customer for every value stored in invoice. Referential integrity is checked by using foreign key constraints. Foreign keys are a kind of constraint where a column in a table in our example, the invoice. The foreign key column in the referencing table can only have values that exist in the primary key of the referenced table. It explains foreign keys, how to implement them, and how they are related to primary keys.
Every time a primary key is created, the database creates an index on that table. The index key is composed of the same column s as the primary key. This index is created implicitly, as part of the primary key creation. In some databases, a PK index is created as a unique key index, to avoid the duplication of primary key values.
At the same time, every query using a search criteria based on the primary key columns which frequently happens can be solved very quickly by the index. For example:. When tables have many records, an index allows them to solve queries faster; however, to use an index there must be a match between the query search criteria and the index key.
Before closing, I want to take advantage of the fact that perhaps some readers are interested in a data engineering career. I suggest the article New Learning Path: Data Engineering to learn about what a data engineer needs to know. Back to articles list Articles Cookbook. What is a primary key and why is it so important in SQL? Find out in this article.
Tags: sql learn sql primary key. Get to know one of the most important concepts in SQL databases: primary keys. It only takes 10 minutes to learn the basics! Who Is a Data Engineer?
0コメント