What is the relationship between primary and foreign keys?

Foreign key. It must be the primary key of another table.

1.A field or combination of fields that uniquely represents each record in a data table is called a primary key, and a primary key uniquely identifies each row of records in a table, but this is only part of its role.

2.The primary key is used to associate records with data stored in other tables, in this regard, the primary key is a simple pointer between records in different tables, and the primary key is reduced to determine each record in the table.

3.The primary key cannot be a null value, and the only constraint is to specify that the combined value of one or more columns is unique to prevent duplicate values from being entered in the column, so the value of the primary key is meaningless to the user.

4.If there are two tables a, b, and c that are the primary keys of A, and B also has a C field, C is the foreign key of table B, and the foreign key constraint is used to maintain the consistency of data between the two tables.

5.In a database, there is often not just one table, and these tables are not independent of each other, and different tables need to establish a relationship between them in order to communicate their data with each other.

1. The primary key is the only candidate keyword that is selected and used as the row of the table. A table has only one primary keyword. The primary keyword can also be referred to as the primary key.

The primary key can consist of a single field or a combination of multiple fields, which can be a single-field primary key or a multi-field primary key, respectively.

2. Foreign key: If a public keyword is the main keyword in a relationship, then this public keyword is called the foreign key of another relationship. It can be seen that a foreign key represents a related connection between two relationships.

A table with a foreign key of another relationship as the primary keyword is called the master table, and a table with this key is called a slave table of the master table. Foreign keys are also known as foreign keywords.

The original intention of the foreign key reading design is to ensure the consistency and integrity of the operation of the logically related DU table data on the database side.

Pros DAO:

Streamline correlated data and reduce data redundancy.

Avoid additional O&M operations for a large amount of redundancy in the later stage.

Reduce application complexity and additional exception handling.

The management of relevant data is handled by the database side.

Increase the readability of documents.

Especially at the beginning of table design, when drawing an ER diagram, the logic is simple and clear, and the readability is very strong.

Cons: Performance pressure.

Foreign keys generally have cascading functions, cascading updates, cascading deletions, and so on. In scenarios where massive amounts of data are used, there is a lot of performance pressure. For example, if a new record is inserted, if the table in which the record is inserted has 10 foreign keys, it is necessary to check whether the inserted records are reasonable for each of the 10 associated tables, which delays the normal insertion of the record.

In addition, the update of the parent table will add relevant locks to the child table.

Other features are not very flexible.

For example, updating the table structure.

1. Ensure the integrity of the entity, speed up the database operation, when adding a record to the table, access will automatically check the primary key value of the new record, and do not allow the value to be duplicated with the primary key value. Access is automatically displayed sorted by primary key value. If there is no constraint, it is displayed in the order in which the user enters the information.

The primary key does not accept null values, and the constraint ensures unique data.

2. The foreign key guarantees the integrity of the data. Foreign key: The primary key of one set of data is the element of another set of data; The primary key restricts that there cannot be a value other than the primary key class in the table where the foreign key is located. A foreign key is used to associate with another table.

It is a field that can determine the record of another table, such as a table, to maintain the consistency of the data. For example, if a field in table A is the primary key of table B, it can be a foreign key of table A.

The primary key is the primary key of the table, which is unique and non-empty, while the foreign key is the type of a field in another table and the field name of the same field, which is generally used to associate two or more data tables.

Take the following three tables as an example:

There are three tables, one table is reader information, there is an attribute for readno, one table is the information of the book, there is an attribute is bookno, one table is the borrowing relationship, there are two attributes respectively to the readno in the reader information table, and the bookno in the book information table as a foreign key, I want to ask, when inserting data in the borrowing relationship table, don't you have to write readno and bookno yourself, in this way, what is the use of setting a foreign key?

Foreign key value rules: null values or primary key values of references.

1) If you want to insert a non-null value, you cannot insert it if it is not in the primary key table.

2) When updating, you cannot change the value that is not in the main key table.

3) When deleting the primary key table records, you can choose whether to delete the foreign key records together or refuse to delete them together when creating foreign keys.

4) When updating a primary key record, there is also the option to cascade update and reject execution.

In short: it serves as a constraint, that is, only the values that exist in the reader's book information table can be inserted into the borrowing relationship table.

Otherwise, something will go wrong.

The effect is that if you insert a readno or bookno that is not in the two tables, it will not be inserted.