1. Superkey
A super key is the set of attributes that can uniquely determine the data in each row in the table. It can be a single attribute or a combination of multiple attributes.
Example:
A student table contains four fields: student number (ID), name (Name), age (Age) and class (Class).
Among them, the student ID (ID), name + class (Name + Class) combination, and the complete set of student ID + name + age + class can all be used as super keys because they can uniquely identify a row of data in the table.
| ID | Name | Age | Class |
|---|---|---|---|
| 001 | Zhang San | 20 | Class 1 |
| 002 | Li Si | 21 | Class 2 |
| 003 | Wang Wu | 20 | Class 1 |
| 004 | Zhao Liu | 22 | Class 3 |
2. Candidate Key
The candidate key is the smallest super key, i.e. the super key without any redundant attributes. A table can have multiple candidate keys.
Example:
In the above table, if the student number (ID) is assumed to be unique and no other attributes can remain unique after combining with the student number, the student number (ID) is a candidate key. Meanwhile, if the name+class combination is also unique in the table (although this is less common), then it is also a candidate key.
3. Primary Key
The primary key is a special candidate key in the table that uniquely identifies each row of data. Each table can only have one primary key, and the value of the primary key cannot be empty.
Example:
In the above table, we usually choose the student ID as the primary key because it uniquely identifies each student and meets all requirements of the primary key.
4. Foreign Key
A foreign key is a field in a table whose value must be the value of the primary key of another table. It is used to establish associations between two tables to ensure reference integrity of the data.
Example:
Suppose we have a class table (Class), which contains two fields: Class ID (ClassID) and Class Name (ClassName), and the class ID is the primary key.
In the student table, we can add a Class ID (ClassID) field as a foreign key. The value of this field must correspond to a class ID in the class table, thereby establishing an association between the student table and the class table. In this way, when we query a student's information, we can quickly find the class information to which the student belongs through the class ID in the student table.
Class Table (Class)
| ClassID | ClassName |
|---|---|
| C001 | Class 1, Grade 1 |
| C002 | Class 2, Grade 1 |
| C003 | Grade 2 Class 1 |
Student Table (Student)
| ID | Name | Age | ClassID |
|---|---|---|---|
| 001 | Zhang San | 20 | C001 |
| 002 | Li Si | 21 | C002 |
| 003 | Wang Wu | 20 | C001 |
| 004 | Zhao Liu | 22 | C003 |
To put it simply, super keys contain candidate keys. The most commonly used primary keys are the ones, while foreign keys are used for inter-table associations.
This is the end of this article about the implementation of super keys, candidate keys, primary keys and foreign keys in mysql database. For more related mysql super keys, candidate keys, primary keys, foreign keys, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me more in the future!