Database principles-Foreign keys and primary keys

In the relational database, the data structure has logical structure and physical structure. Physical structure refers to the structure of data files stored on physical media. A logical structure is a relationship, which is a two-dimensional table of sheets. A column in a table is a field (property) that represents an attribute of the entity. A row in the table is a record. For example, in the Student table (school number, name, age, gender), there are 4 fields in the table that represent the 4 attributes of the student entity. A row of data in the table (001, Zhang San, male, 20), which is a record, represents the information of the student Zhang San.

In a table, the set of fields used to uniquely identify a record, called the primary key or primary key, or the primary key (the main code), and the primary key contains the attribute (field) called the primary attribute, and the other is the non-primary attribute.

It is explained here that since it is a "field set", the primary key can be a field or multiple fields. For example, the student table above, with an underscore number, is a field that uniquely identifies a student, and the Number field is the primary key of the table. Since the name of the student is unavoidable, the name is generally not used as the primary key to uniquely identify a student.

Let's take another example to illustrate multiple fields as primary keys. For example, students choose the timetable (school number, course number, score), in the students choose the curriculum, the main key word is (student number, course number). The reason is obvious, a student can choose multiple courses, a course can be selected by multiple students. It is not enough to uniquely identify a record if the number or course number is used alone.

For the foreign key understanding, here I also give an example. Suppose there are two tables, student tables (school number, name, age, gender, professional number), professional information sheet (professional number, professional name, professional notes information). The primary key in the student table is the school number, and the primary key in the Professional information table is the professional number. The non-primary attribute professional number in the student table happens to be the primary key in the Professional information table. We call this professional number a foreign key to the student's table. As such, the non-primary property of one table is the primary property of the other table, which is the foreign key.

There are three types of database constraints, Entity integrity constraints, referential integrity constraints, and user-defined constraints.

1. Entity integrity constraints refer to the primary key cannot be empty, and how to uniquely identify a record if the primary key is empty.

2. Referential integrity constraints, which are foreign key constraints, the value of a foreign key must exist in the primary key field that it references. For example, the value of the professional number attribute in the student table must be stored in the professional number attribute in the Professional information table. Think about it and understand how a student (college student) might belong to a non-existent profession.

3. User-defined integrity constraints, refers to some of the user's own set of constraints, such as whether the field can be empty, the value of the field value range (such as: Man's gender can only take male, female).

Database principles-Foreign keys and primary keys