Table's primary key and foreign key

A relational table is actually a collection of a class of entities with common attributes. As defined by the collection, the elements in the collection cannot be duplicated. Similarly, there should be no duplicate records in the relational table. For example, in a student's table, it is not necessary or appropriate to have two records of the same student.

In the real world, in addition to the information of the things themselves, there is still a lot of connection between things, which reflected in the database is reflected in the relationship between the table. In Figure 1, for example, student tables store student information, which is the information stored in the system. But there is a connection between the student table and the table: Each student belongs to a department, and each department can contain more than one student, so there is a "belong" relationship between the student table and the table.

(Fig. 1)

In a relational database, how do you store information about this contact? The use of "foreign key" implementation. If one of the columns in a table is a primary key in another table, it is called a foreign key. For example, in Figure 1 the student table "line number" is the foreign key, because the line number is the primary key of the table (the primary foreign key relationship is established if the two tables have the same fields and attributes). The "Department number" column is designed in the student table to store the contact information between students and departments.

A foreign key is a link that connects two tables. Through the equivalent connection of the foreign key and the primary key, as shown in Figure 1, the relevant records of different tables can be connected together, so that the relevant data in the database is found. Using foreign keys, you can query the information of each student's department, or you can query the student information contained in the Department you are developing.

When two tables are connected by a foreign key-primary key, it is necessary to maintain consistency between the two table data. For example, while inserting a student record, the value of the foreign key (the line number) must be a valid value for the primary key in the system (must have this system), or a null value (the student's department is not determined), or, in the case of deleting the table record, if there is a record of the student in the Student table (the department also has students in the school), then the

An example of an employee information management system is analyzed below. The database has an employee basic information table (person), a departmental Code table (Deparment), and a School Education Code table (education), as shown in Figure 2:

(Fig. 2)

The Department Code table holds the department number and department name, the department number (DEPID) is the primary key, and the number of each department is unique in the table, so that each row can be identified by a primary key.

The Education Code table holds the academic number and the education name, the education number (EDUID) is the primary key.

The Employee basic Information table stores the basic information of the employee, including the work permit number, name, department number, job title, salary, education number, etc., and the ID is the primary key of the table. In this table, the Department column is a foreign key that matches the Depid primary key in the Department Code table, and the Education column is also a foreign key that matches the Eduid primary key in the education-encoded table. Using the foreign key and the primary key connection, you can query out someone, such as Zhang San Department is manager room, his degree room master.

The use of "foreign key-primary key" connection can better win the simplification of database design process, reduce data redundancy, improve database efficiency.