Databases are used in any activity: in the banking and financial sectors, tourism, warehousing, manufacturing and training. They are a collection of tables, have clear properties and are subject to strict requirements. In relational databases, tables are called relationships.
What is the primary key in the database
In the database, the primary key of the table is one of its columns (Primary key). Let's look at an example of how this looks. Imagine a simple attitude of university students (let's call it “Students”).
| Surname | Name | Year of birth | Faculty | Course | Grade point average |
| Ivanov | Ivan | 1999 | Economic | 3 | 3.8 |
| Kuznetsov | Peter | 2000 | Journalism | 2 | 4.1 |
| Mikhailov | Sergei | 2000 | Technological | 2 | 4.0 |
| Kozlov | Evgeny | 1998 | Technological | 4 | 4,5 |
| Ivanov | Anton | 2000 | Legal | 2 | 3.6 |
We need to uniquely identify the student by one column. For this, the information in this column for each record must be unique. But the available data in this regard does not allow us to uniquely identify the record, since namesake namesakes, namesakes and students with the same surnames and names can study on the same course and one faculty. The primary key in the database serves to accurately determine the required row in the relation. Most often, a numeric field is used in this capacity, automatically increasing with the entry (auto-incrementing identifier column).
Simple and Composite Primary Key
Primary key can be simple and compound. If the uniqueness of a record is determined by the value in only one field, as described above, we are dealing with a simple key. A composite key is a primary database key that consists of two or more fields. Consider the following attitude of bank customers.
| FULL NAME. | Date of Birth | Passport Series | Passport ID |
| Ivanov P.A. | 05/12/1996 | 75 | 0553009 |
| Sergeev V.T. | 07/14/1958 | 71 | 4100654 |
| Krasnov L.V. | 01/22/2001 | 73 | 1265165 |
Passports of people may contain the same series or numbers, but passports with the same combination of series and number do not exist. Thus, the fields “Passport Series” and “Passport Number” will become the composite key of the indicated relationship, uniquely identifying the person.
Relationships Between Relationships
So, the primary key in the database is one or more columns of the table, allowing you to uniquely identify the row of this relationship. Why is it needed?
Let's get back to the first example with the “Students” attitude. In addition to this relationship, the database also stores other information, for example, the performance of each student. In order not to repeat all the information that is already contained in the database, they use the key, referring to the desired record. It looks like this.
In two respects of the example, we see the ID field. These are the primary keys in the database for these tables. As you can see, the performance contains only links to these fields from other tables without the need to indicate all the information from them.
Natural and surrogate key
How to determine the primary key of a database table? The two examples we examined — Students and Bank Clients — illustrate the concepts of a natural and surrogate key. In the table of bank customers, we determined the key, consisting of the fields "Number" and "Passport Series", using the existing columns. Such a key is called natural; for its definition we did not make any changes or additions. In the case of the Students relationship, not a single field or combination of fields gave us uniqueness. This forced us to enter an additional field with the student code. Such a key is called a surrogate key, for it we have added another service column to the table. This column does not provide any useful information and is only used to identify records.
Foreign key and data integrity in the database
All of the above leads us to a foreign key (Foreign key) and database integrity. Foreign key is a field that refers to the Primary key of an external relationship. In the grades table, these are the Student and Discipline columns. Their data sends us to external tables. That is, the field "Student" in relation to "Performance" is a Foreign key, and in relation to "Student" it is the primary key in the database.
An important principle of building databases is their integrity. And one of its rules is link integrity. This means that the foreign key of the table cannot refer to the nonexistent Primary key of another relation. You cannot delete a record with the code 1000 — Ivan Ivanov from the Student relationship if it is referenced by a record from the gradebook. In a properly built database, when you try to delete, you get an error that this field is used.
There are other groups of integrity rules, as well as other database restrictions that also deserve attention and must be taken into account by developers.