Analysis on the difference between super key, primary key and candidate key in MySQL

This article brings you relevant knowledge about mysql. It mainly introduces the differences between super keys, primary keys and candidate keys in MySQL. It has a good reference value. I hope it will be helpful to everyone. Helps.

Recommended learning: mysql video tutorial

About the difference between super key, primary key and candidate key

Recently, I encountered a question when reading a MySQL book:

Since we already have the concept of primary key, and the primary key can already meet the needs, why do we still need such a thing as a candidate key? What is the role of candidate keys? It's given a candidate key definition but it doesn't really mess around with anything.

With the mentality of asking for more information, I searched online and looked at the explanations of the masters. I was still a little confused, so I wanted to sort it out here to help myself understand more clearly. , I also hope that if there is any misunderstanding, someone can give me some pointers Here are some of my understandings:

In fact, primary keys and super keys are better. For example, there is such a table:

#The writing in the table is a bit rough, so I’ll just take a look at it.

Super key

The set of attributes that can uniquely identify a tuple in a relationship is called the super key of the relational schema. Note the "attribute set" in the definition. The super key can be a large set, as long as it can determine which row it is, so 'id', 'user', 'pwd', 'section', 'name' are all OK is the set of superkeys.

Candidate key

Super key that does not contain redundant attributes. For example, in the above super key, 'id' can determine which row it is by itself, so it can be a candidate key by itself. , the other four besides it can also be candidate keys, but these five are put together because there are redundant columns, they are not candidate keys. (The other four can be candidate keys because each column may have duplicate content)

Primary key

Find one of all candidate keys for use as the primary key, that is to say It can be an id, or a combination of the other four, or other choices, as long as it can ensure that the selected set can be uniquely determined.

In summary, candidate keys are a subset of super keys, and primary keys are a subset of candidate keys.

In fact, after sorting out so much, I still don’t understand what the function of the candidate key is. In fact, it may be like a waiting hall. There is only one seat left in the car, and there are several primary keys in the waiting hall. Sit down and tell you: "We have all bought tickets and are eligible to get on the bus. Whoever you choose to get on the bus will get on the bus with you." That should be it.

Understand the concepts and relationships of super keys, candidate keys, primary keys

Basic concepts

• Super key: A key that uniquely identifies a tuple in a relationship The set of attributes is called the superkey/key of the relational schema.
• Candidate key: A superkey that does not contain redundant attributes is called a candidate key, that is, its true subset is no longer a superkey.
• Primary key: A candidate key selected by the user as a tuple identifier is called the primary key and is one of the candidate keys.

Relationship

The candidate key is a subset of the superkey, and the primary key is one of the candidate keys.

As an example

Consider the attribute set (ID number, name, gender, age), assuming there are no duplicate names

a. The super keys are:

• ID number, name, (name, gender), (name, gender, age), etc.
• --It can be seen here that the super key can uniquely Determine a person’s attribute group

b. The candidate key in the super key

• is unique in ID number and name, and has no redundant attributes. So it is a candidate key
• --It can be seen here that the candidate key is a super key without redundant attributes

c. Select the primary key

• Users can choose their name or ID number as the primary key according to their own preferences
• --The primary key is a selected candidate key

Still don’t understand?

Example:

In the SQL Server database, there is a student information table as shown below. The set of attributes that cannot be used as candidate keys in this table is ( ) ( Select one)

Student ID Name Gender Age Department Major
20020612 Li Huinan 20 Computer Software Development
20060613 Zhang Mingnan 18 Computer Software Development
20060614 Wang Xiaoyu Female 19 Physical Mechanics
20060615 Li Shuhua female 17 Biological Zoology
20060616 Zhao Jingnan 21 Chemistry Food Chemistry
20060617 Zhao Jing female 20 Biological Botany

a) {Student ID}
b) {Student number, name}
c) {Age, department}
d) {Name, gender}
e) {Name, major}

Recommended learning: mysql video tutorial

The above is the detailed content of Analysis on the difference between super key, primary key and candidate key in MySQL. For more information, please follow other related articles on the PHP Chinese website!