Database-primary key and foreign key and its constraints

1: What is a primary key

In a table, the set of fields used to uniquely identify a record is called the primary key or primary key, or the primary key (or main code), which says "field set" because the primary key may be represented by a field or multiple fields. For example:

Student Table (School number, name, gender, professional number), where the study number is the primary key, a student ID can uniquely identify a students information. Another table: Students choose the timetable (school number, course number, score), here (school number, course number) is the primary key, because a student number (i.e. a pupil) can choose multiple courses, a course number (that is, a course) can be selected by multiple students, unable to use a field to identify a student selection of information records, and use (student Course number) These two fields can be used as keywords to uniquely identify a student's course selection information.

2: What is a foreign key?

The non-primary property of a table is the foreign key of the table, which is the primary property of the other table. Here are two: Student table (school number, name, 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.

Concept definition: What is a parent table and a child table?

There are two tables, A and B, and a field ID in table A is a foreign key, and a field ID in table B is the primary key, then B is the parent table and A is the child table. That is, a Table foreign key field (equivalent to a table here) is the primary key in another table (equivalent to the B table here). Or the above two tables to understand: Student table (school number, name, gender, professional number), professional information table (professional number, professional name, professional notes information). Here the student table is a child table, and the Professional information table is the parent table.

The purpose of setting a table's foreign key is to establish a connection to the parent table, such as the ID of a professional number in the Professional information table is ' 1001 ', and when the ID is deleted, the record with the professional ID of ' 1001 ' in the student table is deleted, and this is done to ensure the integrity of the table.

Three constraints on the database 1. The entity integrity constraint refers to a primary key that 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-primary key and foreign key and its constraints