How to understand primary keys, foreign keys and indexes in databases

The primary key in the database refers to the attribute or attribute group that can uniquely identify a record. The foreign key refers to the primary key in another table that serves to establish a connection with other tables. The index is used to quickly find specific values. Records

[Recommended courses:Database tutorial】

Primary key in the database

The primary key in the database refers to the attribute or attribute group that can uniquely identify a record in an attribute group. There can be only one primary key in a table and it cannot be repeated or null. Primary keys often form referential integrity constraints with foreign keys to prevent data inconsistencies. A unique index is automatically generated for the primary key in the database management system, so the primary key is a special index

Example:

学生表（学号，姓名，班级）

Since each student's student number is unique, the student number is a primary key

课程表(课程ID,课程名)

Since the course ID is unique, it is a primary key

成绩表(学号,课程ID,成绩)

A single attribute in the score table cannot identify a record. It requires a combination of student number and course ID. Plays the role of identifying records, so the combination of the two is a primary key

Foreign keys in the database

Foreign keys are used to establish relationships with another table Association is a field that determines records in another table. The foreign key is the primary key of another table. It can be multiple and repeated, or it can be a null value. The purpose of foreign keys is to keep the data in the table consistent

Example:

In the above case, the student number is not the primary key in the grades table but it is the primary key in the student table. Therefore We can call the student number in the score table the foreign key of the student table

The index in the database

The index refers to the quick search Records with specific values. The purpose is to facilitate retrieval and speed up access. It is created according to certain rules and plays a role in sorting.

The difference between primary key, foreign key and index

The difference between these three As shown below

##Primary keyForeign key IndexDefinitionUniquely identifies a record, no duplicates or null values ​​are allowed is the primary key of another table, duplicates and null values ​​are allowedNo duplicate values But there can be null valuesFunctionUsed to ensure data integrityEstablish connections with other tablesImprove access SpeedNumberThere can only be one primary keyThere can be multipleThere can be multiple unique indexes Summary: The above is the entire content of this article. I hope it will be helpful to everyone's study.

The above is the detailed content of How to understand primary keys, foreign keys and indexes in databases. For more information, please follow other related articles on the PHP Chinese website!