What types of keys are there in the database?

Keys play an important role in relational databases and are an important part of the relational database model. The following article will introduce you to some common keys in databases. I hope it will be helpful to you.

Why do we need to use keys?

In practical applications, the number of tables required to store data is large, and different tables are also related to each other. [Related video recommendations: MySQL Tutorial]

In addition, a large amount of data is stored in the table. Tables often scale to thousands of records stored in them, unclassified and unorganized.

Now to get any specific record from such a dataset you will have to apply some conditions but if there are duplicate data and every time you try to get some data by applying some conditions you will get error data. How many trials are there before correct data is obtained?

To avoid all this, define keys to easily identify any data row in the table.

In a database, keys are used to establish and identify relationships between tables, and are also used to uniquely identify any record or data row in a table. It can be a single attribute or a set of attributes, where the combination can serve as a key.

Types of keys

Let’s introduce some common types of keys to you. In order to let you understand these keys, let’s introduce them through examples. :

student_id	name	age	college_id	phone
1	小华	20	01	1561258xxx
2	小红	21	01	1771258xxx
3	小明	20	02	1831258xxx

It can be seen that this is a simple Student table with fields student_id, name, subject_id, age and phone.

Candidate Key (Candidate Key)

A candidate key is defined as the smallest set of fields that can uniquely identify each record in the table. It is an attribute or set of attributes that serves as the primary key of a table to uniquely identify each record in that table.

In the above Student table, student_id and phone are both candidate keys for the student table. The following are some characteristics of candidate keys:

● A candidate key can never be NULL or empty; its value should be unique.

● All attributes in the table except the primary key can be regarded as candidate keys. A table can have multiple candidate keys.

● Candidate key can be a combination of multiple columns (attributes).

Super Key

Super key is defined as a set of attributes in the table that can uniquely identify each record in the table. Super keys are a superset of candidate keys.

In the table defined above, the super key can be:

● student_id: Because each row of student_id data is unique, it can be used to uniquely identify each row.

● (student_id, name): Now the names of two students can be the same, but their student IDs cannot be the same, so this combination can also be a key.

● phone: Because each student’s phone number is unique, phone can also be a key.

So they can all be super keys.

Primary Key

The primary key is the most suitable candidate key to become the identification key of any table. It can uniquely identify each record in the table.

For the Student table, we can use the student_id column as the primary key.

Composite key

A composite key is two or more attributes that uniquely identify any record in a table composed of keys. When no single attribute of the table uniquely identifies a row, we need to use a composite key.

Note: Properties that together form a composite key are not independent or separate keys.

student_id	subject_id	exam_name	mark
1	02	PHP Course	90
2	02	MySQL Course	80

Above is a Score table, which is used to store the scores obtained by students in specific subjects.

In this table student_id, subject_id will form the primary key, so it is a composite key.

Alternative key

Among all candidate keys, only one will be selected as the primary key, and the remaining keys are called alternative keys or auxiliary keys.

Foreign Key

Foreign key is an attribute value in a table that serves as the primary key of another table. Therefore, foreign keys can be used to link two tables together.

#Note: You need to be very careful entering data in foreign key columns, as entering incorrect data may invalidate the relationship between the two tables.

The above is the entire content of this article, I hope it will be helpful to everyone's study. For more exciting content, you can pay attention to the relevant tutorial columns of the PHP Chinese website! ! !

The above is the detailed content of What types of keys are there in the database?. For more information, please follow other related articles on the PHP Chinese website!