Detailed explanation of the definition and usage of super keys, primary keys, foreign keys, etc. in the database

This article mainly introduces you to the specific definitions and uses of super keys, candidate keys, primary keys, primary attributes, and foreign keys in the MySQL database. For detailed explanation of the following content, you can also refer to the MySQL Video Tutorial and MySQL Manual on the PHP Chinese website.

First look at the definitions of various keys:

Super key: The set of attributes that can uniquely identify a tuple in a relationship is called the super key of the relational schema

Candidate key: A superkey that does not contain redundant attributes is called a candidate key

Primary key: A candidate key selected by the user as a tuple identifier. Program primary key

Foreign key (foreign key) If a certain attribute set in relation schema R1 is not the primary key of R1, but the primary key of another relation R2, then the attribute set is the foreign key of relation schema R1.

Detailed explanation combined with examples:

Assume there are two tables as follows:

学生（学号，姓名，性别，身份证号，教师编号）
教师（教师编号，姓名，工资）

Super key:

It can be known from the definition of super key , any combination of student number or ID card number in the student table is the super key of this table. For example: (student number), (student number, name), (ID card number, gender), etc.

Candidate key:

The candidate key is a super key. It is the smallest super key. That is to say, if any attribute in the candidate key is removed, it will no longer be a super key. The candidate keys in the student table are: (student number), (ID card number).

Primary key:

The primary key is one of the candidate keys, which is artificially specified. For example, in the student table, we usually use "student number" as the primary key, and in the teacher table, "teacher number" "Make the primary key.

Foreign key:

The foreign key is relatively simple. The foreign key in the student table is the "teacher number". Foreign keys are mainly used to describe the relationship between two tables.

Primary attributes and primary keys

Strictly speaking, primary attributes: refers to the primary key column, that is, the primary key consists of one column. Primary key definition: an attribute or attribute set that can uniquely identify a tuple, that is, it can be composed of multiple Composed of columns.
In teaching, most examples are that the primary key consists of one column, so it can be simply said that there is no difference between the primary attribute and the primary key.

First Normal Form (1NF)

The first normal form means that each column of the database table is an indivisible basic data item, and there cannot be multiple values ​​in the same column, that is, in the entity An attribute cannot have multiple values ​​or duplicate attributes. This is the basic concept of the first normal form.

What does it mean? For example, there is a "phone" field in a table, and its value includes both a mobile phone number and a landline number. Then this does not meet the requirements of the first paradigm. At this time, "telephone" should be split into "mobile phone" and "landline". This will become a data table that conforms to the first paradigm.

Summary: Fields cannot be divided.
Second Normal Form (2NF)
The second normal form is established on the basis of the first normal form, that is, to satisfy the second normal form, you must first satisfy the first normal form. Second normal form requires that each instance or row in a database table must be uniquely distinguishable. In order to achieve differentiation, it is usually necessary to add a column to the table to store the unique identification of each instance, requiring that the attributes of the entity completely depend on the primary key. The premise for discussing whether a table symbol does not conform to the second normal form is that the primary key of the table is a composite primary key. If it is not a composite primary key, then it cannot be said that the symbol does not comply with the second normal form.
Summary: Partial dependence is not allowed, which means that when a table has a composite primary key, other non-primary key fields must completely depend on the primary key.
Third Normal Form (3NF)

The third normal form must conform to the second normal form. The third normal form means that non-primary key fields in other data tables cannot exist in a data table. That is to say, if there are other The fields in the table must be the primary key of that table.

Suppose a table has three fields a b c, a—>b b—>c. Then c and a are transitive dependencies, which does not conform to the third normal form. And regardless of whether a can directly determine c, it is a transitive dependency, so only b and c do not depend on any field other than a to comply with the third normal form.

Summary: There cannot be transitive dependencies, which means that fields other than the primary key must depend on the primary key and cannot depend on other fields.

The above is the detailed content of Detailed explanation of the definition and usage of super keys, primary keys, foreign keys, etc. in the database. For more information, please follow other related articles on the PHP Chinese website!