|
Hello, I am safe and sound. |
Article directory
- Foreign keys and joins: how to do related queries?
-
- How to create foreign key?
- connect
- Misunderstandings in related queries
Foreign keys and connections: How to do related queries?
In the actual database application development process, we often need to associate two or more tables to obtain the required data. This is because, in order to improve access efficiency, we will store the information of different business modules in different tables.
However, from a business perspective, we need complete and comprehensive information to provide data support for business decisions.
Taking the supermarket project as an example, the sales flow table in the database generally only saves the information necessary for sales, such as product number, quantity, price, amount, membership card number, etc. However, it is not enough to include only this information in the statistical reports presented to supermarket operators, such as product numbers and membership card numbers. These numbers cannot be understood by operators.
Therefore, it is necessary to extract product information from the product information table and member information from the membership table, so as to form a complete report. Thisoperation of querying data scattered in multiple different tables is called multi-table query.
However, multi-table query is not simple. We need to establish relationships between multiple tables before we can query. We also need to avoid common errors in related table queries. How to do this specifically, please read on:
The purchase module in the supermarket project has two such data tables, namely the purchase order header table (importhead) and the purchase order details table (importdetailes). We need to perform CRUD operations on these two tables every day.
The purchase order header table records the overall information of the entire purchase order:
The purchase order detail table records the details of each purchase. A single purchase data record corresponds to the detailed data of multiple purchased goods, which is the so-called one-to-many relationship, as shown in the following table:
Now we need to query all relevant data for a purchase, including the overall information of the purchase order and the details of the purchased goods. In this way, we need to associate the two tables. How to operate?
In MySQL, in order to associate two tables, two important functions are used, namely foreign key and join.
Foreign keys need to be defined during the table creation stage. Connections can connect two tables through fields with the same meaning and are used in the query stage.
How to create a foreign key?
First, let’s understand what a foreign key is?
Suppose we have two tables, Table A and Table B. They are related through a common field id. We call this relationship R. If id is the primary key in table A, then table A is the primary table in this relationship R. Correspondingly, table B is the slave table in this relationship, and the id in table B is what table B uses to reference the data in table A, which is called a foreign key.
Therefore, aforeign key is the public field used in the slave table to reference data in the master table.
For easy understanding, please look at the picture below:
In MySQL, foreign keys are defined through foreign key constraints. A foreign key constraint is a type of constraint.It must be defined in the slave table, including indicating which field is the foreign key field and what is the primary key field in the master table that the foreign key field refers to.
The MySQL system will monitor the deletion of data in the main table based on the definition of foreign key constraints. If it is found that the main table record to be deleted is being referenced by the foreign key field of a record in the slave table, MySQL will prompt an error, thus ensuring that the associated data will not be lost.
Foreign key constraints can be defined when creating a table or by modifying the table. The syntax structure is as follows:
[CONSTRAINT <foreign key constraint name>] FOREIGN KEY field name REFERENCES <main table name> field name
We can define foreign key constraints when creating the table:
CREATE TABLE FROM TABLE NAME ( field name type, ... -- Define foreign key constraints, indicating the foreign key fields and referenced main table fields CONSTRAINT foreign key constraint name FOREIGN KEY (field name) REFERENCES main table name (field name) )
Of course, we can also define foreign key constraints by modifying the table:
ALTER TABLE slave table name ADD CONSTRAINT constraint name FOREIGN KEY field name REFERENCES main table name (field name);
Under normal circumstances, the relationships between tables are designed in advance, so foreign key constraints are defined when the table is created. However, if you need to modify the design of the table, such as adding new fields or adding new relationships, but there are no predefined foreign key constraints, then you must supplement the definition by modifying the table.
Next, let’s take a look at how to create foreign key constraints:
First create the main table demo.importhead:
create table demo.importhead ( listnumber int primary key, supplierid int, stocknumber int, importtype int, importquantity decimal(10, 3), importvalue decimal(10, 2), recorder int, recodingdate datetime );
Then create the demo.importdetails table and define foreign key constraints for it:
create table demo.importdetails ( listnumber int, itemnumber int, quantity decimal(10,3), importprice decimal(10,2), importvalue decimal(10,2), -- Define foreign key constraints, indicating the foreign key fields and referenced main table fields constraint fk_importdetails_importhead foreign key (listnumber) references importhead(listnumber) );
By running this SQL statement, we define a foreign key constraint named “fk_importdetails_importhead” while creating the table. At the same time, we declare that the field “listnumber” of this foreign key constraint refers to the field “listnumber” in the table importhead.
We can view the relevant information of foreign key constraints through MySQL’s own database for storing system information: information_schema:
mysql> SELECT
-> constraint_name, -- represents the name of the foreign key constraint
-> table_name, -- indicates the name of the data table to which the foreign key constraint belongs
-> column_name, -- the field name representing the foreign key constraint
-> referenced_table_name, -- indicates the name of the data table referenced by the foreign key constraint
-> referenced_column_name -- indicates the field name referenced by the foreign key constraint
-> FROM
-> information_schema.KEY_COLUMN_USAGE
-> WHERE
-> constraint_name = 'fk_importdetails_importhead';
+----------------------------- + --------------- + --- ---------- + ----------------------- + --------------- --------- +
| CONSTRAINT_NAME | TABLE_NAME | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+----------------------------- + --------------- + --- ---------- + ----------------------- + --------------- --------- +
| fk_importdetails_importhead | importdetails | listnumber | importhead | listnumber |
+----------------------------- + --------------- + --- ---------- + ----------------------- + --------------- --------- +
1 row in set (0.05 sec)
Through the query, we can see that the table where the foreign key constraint is located is “importdetails”, the foreign key field is “listnumber”, the referenced main table is “importhead”, and the referenced main table field is “listnumber”. In this way, by defining foreign key constraints, we have established an association between the two tables.
After the relationship is established, how can we obtain the data we need? At this time, we need to use connection query.
Connect
In MySQL, there are two types of connections, namely inner join and outer join.
Inner join means that the query results only return records that meet the connection conditions. This connection method is more commonly used;Outer joins are different, which means that the query results return all records in a certain table and records in another table that meet the join conditions.
Let’s get to know inner joins:
In MySQL, the keywords join, inner join, and cross join have the same meaning, and they all represent inner joins. We associate the two tables through join to query the data in the two tables.
There is a need for member sales in the supermarket project, so the data records in our flow sheet include both ordinary sales by non-members and member sales. The difference between them is that the data records of member sales include the member number, while in the data records of non-member sales, the member number is empty.
The following is the sales table demo.trans. The actual sales table is relatively complex. In order to facilitate understanding, the table is simplified. It is assumed that the business field cardno is the primary key of the member information table. The simplified structure is as follows:
Take another look at the simplified member information table:
There is an association between these two tables, and the field cardno in the table demo.trans is the foreign key in this association.
We can query the transaction records of all member sales through inner connections:
select a.transactionno, a.itemnumber, a.quantity, a.price, a.transdate, b.membername from demo.trans as a join demo.membermaster as b on (a.cardno = b.cardno);
We related the two tables together through the public field cardno, and queried the member consumption data.
Here, the keyword join and the keyword on are used in pairs, which means to query all the records in the two tables that meet the association condition “demo.trans” The value of cardno in the table is equal to the value of cardio in the demo.membermaster table.
The above content explains inner connections, let’s talk about outer connections next.
Unlike inner joins, which only return data records that meet the connection conditions, outer joins can also return all records in the table. They include two types, namely left joins and right joins.
Left join: generally abbreviated as left join, returns all data records in the left table, as well as records in the right table that meet the connection conditions;Right join: Generally abbreviated as right join, it returns all data records in the right table and records in the left table that meet the join conditions.
When we need to query all pipeline information, we will use external connections. The code is as follows:
select a.transactionno, a.itemnumber, a.quantity, a.price, a.transdate, b.membername from demo.trans as a left join demo.membermaster as b -- left join mainly demo.trans on (a.cardno = b.cardno);
As you can see, I used LEFT JOIN, which means that the data records in the table demo.trans are mainly used. All data records in this table must appear in the result set, and at the same time, the connection conditions are given (a.cardno=b. cardno) The value of the field membername in the table demo.membermaster.
We can also use RIGHT JOIN to achieve the same effect, the code is as follows:
select a.transactionno. a.itemnumber, a.quantity, a.price, a.transdate, a.membername from demo.membermaster as b right join demo.joins as a -- right join, the order is reversed, demo.trans is still the main one on (a.cardno = b.cardno);
View the results after running:
mysql> SELECT
-> a.transactionno,
-> a.itemnumber,
-> a.quantity,
-> a.price,
-> a.transdate,
-> b.membername
-> FROM
-> demo.trans AS a
-> LEFT JOIN -- left join
-> demo.membermaster AS b ON (a.cardno = b.cardno);
+ --------------- + ------------ + ---------- + ------- + - -------------------- + ------------ +
| transactionno | itemnumber | quantity | price | transdate | membername |
+ --------------- + ------------ + ---------- + ------- + - -------------------- + ------------ +
| 1 | 1 | 1.000 | 89.00 | 2020-12-01 00:00:00 | Zhang San |
| 2 | 2 | 1.000 | 12.00 | 2020-12-02 00:00:00 | NULL |
+ --------------- + ------------ + ---------- + ------- + - -------------------- + ------------ +
2 rows in set (0.00 sec)
mysql> SELECT
-> a.transactionno,
-> a.itemnumber,
-> a.quantity,
-> a.price,
-> a.transdate,
-> b.membername
-> FROM
-> demo.membermaster AS b
-> RIGHT JOIN -- Right join
-> demo.trans AS a
-> ON (a.cardno = b.cardno);
+ --------------- + ------------ + ---------- + ------- + - -------------------- + ------------ +
| transactionno | itemnumber | quantity | price | transdate | membername |
+ --------------- + ------------ + ---------- + ------- + - -------------------- + ------------ +
| 1 | 1 | 1.000 | 89.00 | 2020-12-01 00:00:00 | Zhang San |
| 2 | 2 | 1.000 | 12.00 | 2020-12-02 00:00:00 | NULL |
+ --------------- + ------------ + ---------- + ------- + - -------------------- + ------------ +
2 rows in set (0.00 sec)
Misunderstandings in related queries
With the connection, we can perform related queries between the two tables. There is a problem:
Is it ok if the associated query must be based on foreign key constraints?
In fact, in MySQL, foreign key constraints are not a necessary condition for related queries. Many people often think that when designing tables, they can solve everything by just connecting and querying. Foreign key constraints are too troublesome and unnecessary. If they think this way, they have entered a misunderstanding.
Let’s take the example of supermarket purchasing as an example. Assume that the purchasing data is like this: the supplier number is 1, and the purchasing warehouse number is 1. The product number we purchased is 1234, the purchase quantity is 1, the purchase price is 10, and the purchase amount is 10.
insert into demo.importhead ( listnumber, supplierid, stocknumber, ) values ( 1234, 1, 1 );
Run SQL to view the contents of the table:
mysql> SELECT *
-> FROM demo.importhead;
+ ------------ + ------------ + ------------- + --------- --- + ---------- + ------------- + ------------- +
| listnumber | supplierid | stocknumber | importtype | quantity | importprice | importvalue |
+ ------------ + ------------ + ------------- + --------- --- + ---------- + ------------- + ------------- +
| 1234 | 1 | 1 | 1 | NULL | NULL | NULL |
+ ------------ + ------------ + ------------- + --------- --- + ---------- + ------------- + ------------- +
1 row in set (0.00 sec)
As you can see, we have a purchase order, the order number is 1234, the supplier is supplier No. 1, and the purchase warehouse is warehouse No. 1.
Next, we insert the purchase details data into the purchase order details table:
insert into demo.importdetails ( listnumber, itemnumber, quantity, importprice, importvalue ) values ( 1234, 1, 1, 10, 10 );
Run SQL to view the records in the table:
mysql> SELECT *
-> FROM demo.importdetails;
+ ------------ + ------------ + ---------- + ------------ - +------------- +
| listnumber | itemnumber | quantity | importprice | importvalue |
+ ------------ + ------------ + ---------- + ------------ - +------------- +
| 1234 | 1 | 1.000 | 10.00 | 10.00 |
+ ------------ + ------------ + ---------- + ------------ - +------------- +
1 row in set (0.00 sec)
In this way, we have the detailed data of purchase order No. 1234: the purchased product is product No. 1, the purchase quantity is 1, the purchase price is 10 yuan, and the purchase amount is 10 yuan.
At this time, if I delete the data in the purchase single header table, there will be a situation where only the details and no single header data are missing. Let’s see what happens:
delete from demo.importhead where listnumbere = 1234;
When you run this SQL statement, MySQL will prompt an error because data deletion violates foreign key constraints. MySQL prevents data inconsistencies from occurring.
Another question is the order in which I insert data. Why should I insert the data in the purchase order header table first, and then insert the data in the purchase order details table? If I first insert data into the slave table (purchase order details table), MySQL will not be able to find the referenced master table information and will prompt an error because adding data violates foreign key constraints.
Therefore, although we can perform related queries without foreign key constraints, with it, the MySQL system will protect our data and avoid accidental deletions, thus improving the overall reliability of the system.
Foreign key constraints can help us determine the reference relationship between the foreign key fields in the slave table and the primary key fields in the master table. It can also ensure that the master table data referenced by the data in the slave table will not be deleted, thus The consistency of data in the two tables is guaranteed.
Why can we perform related queries without foreign key constraints in MySQL?
The reason is that foreign key constraints are costly and require consuming system resources. It may not be suitable for large concurrent SQL operations. For example, the central database of a large website may become very slow due to the system overhead of foreign key constraints. Therefore, MySQL allows you to complete the logic of checking data consistency at the application level without using the foreign key constraints that come with the system. In other words, even if you do not use foreign key constraints, you must find a way to implement the function of foreign key constraints through additional logic at the application level to ensure data consistency.
Therefore, we should try our best to develop the habit of defining foreign key constraints in related tables. However, if the business scenario cannot bear the cost of foreign key constraints due to high concurrency and other reasons, it is not necessary to define foreign key constraints. However, the logical functions of foreign key constraints must be implemented at the application level to ensure the accuracy and reliability of the system.