This is the 18th day of my participation in Gwen Challenge
Wechat public number search [program yuan xiaozhuang], pay attention to the halfway program yuan how to rely on Python development to support the family ~
preface
A project if all data will be stored in a table is not reasonable, such as an employee information, only two divisions of the company, employees 100 million people, however, means that the employees that the value of the information in this form need to be repeated storage, great waste of resources, therefore it is possible to define a list of departments and employees associated information table, And the way to do that is a foreign key.
A Foreign Key (Foreign Key)
Store employee information, according to the above table will be a great waste of resources, duplicate data is too much, this problem is similar to write all the code in a py file, so we can turn a table into a different table, between the different tables associated, and establish related will need to use the foreign key foreign key. Foreign keys are also a type of constraint.
How do I determine table relationships
There are three types of relationships between tables: one-to-many, many-to-many, and one-to-one. So how do you determine the relationship between tables?
What does it mean to suggest empathy when determining the relationship between tables? It is to consider from the perspective of two tables, such as the relationship between the employee table and the department table:
Start with the employee list: Can an employee belong to more than one department in the employee list? The answer is no
From the perspective of the department table: can there be multiple employees in a department table? And the answer is yes
Therefore, the employee table and the department table are one-way one-to-many, so the employee table and the department table are one-to-many.
How to establish a table relationship
When establishing a table relationship, the association between tables usually takes the primary key ID as the associated field.
One-to-many relationship – Employee table and department table
There is no many-to-one relationship in MySQL, one-to-many and many-to-one are both one-to-many. There are several points to follow when creating a one-to-many table relationship:
First, the foreign key fields are built on the many side, the employees table
Second, when creating a table, be sure to create the associated party first, that is, the department table
Third, when inputting data, it is also necessary to input the data of the associated table, that is, the data of the department table
Fourthly, when the relationship between different tables is established, cascading update and deletion are required, which can also be called synchronous update and synchronous deletion. If the cascading update and deletion are not established, the associated data in the associated table cannot be deleted or the ID can be changed, because the two tables are associated with each other.
Create associated tables and department tables
mysql> create table bm(
id int primary key auto_increment,
bm_name varchar(10),
bm_desc char(64)); Query OK,0 rows affected (0.01 sec)
mysql> desc bm;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| bm_name | varchar(10) | YES | | NULL | |
| bm_desc | char(64) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
Create table where foreign key resides, employee table
mysql> create table yg(
id int primary key auto_increment,
yg_name varchar(6),
bm_id int.foreign key(bm_id) references bm(id) -- indicates that bm_ID is a foreign key field associated with the ID field in the BM table
onUpdate cascadeon deleteCascade delete); Query OK,0 rows affected (0.10 sec)
mysql> desc yg;
+---------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| yg_name | varchar(6) | YES | | NULL | |
| bm_id | int(11) | YES | MUL | NULL | |
+---------+------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
Insert data
mysql> insert into bm (bm_name, bm_desc) values ('python'.'Life is short'), ('go'.'let us go');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from bm;
+----+---------+--------------+
| id | bm_name | bm_desc |
+----+---------+--------------+
| 1 | python |Life is too short|
| 2 | go | let us go |
+----+---------+--------------+
2 rows in set (0.00 sec)
mysql> insert into yg (yg_name, bm_id) values ('xu'.1), ('zhuang'.2), ('lili'.1);
Query OK, 3 rows affected (0.09 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from yg;
+----+---------+-------+
| id | yg_name | bm_id |
+----+---------+-------+
| 2 | xu | 1 |
| 3 | zhuang | 2 |
| 4 | lili | 1 |
+----+---------+-------+
3 rows in set (0.00 sec)
The data associated with the foreign key must exist in the associated table or an error will be reported
mysql> insert into yg (yg_name, bm_id) values ('xu'.3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`book_manage`.`yg`, CONSTRAINT `yg_ibfk_1` FOREIGN KEY (`bm_id`) REFERENCES `bm` (`id`))
The following error occurs if you do not use cascading update and delete statements.
mysql> update bm set id=5 where id=2; Select * from bm where id=2 and id=5
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`book_manage`.`yg`, CONSTRAINT `yg_ibfk_1` FOREIGN KEY (`bm_id`) REFERENCES `bm` (`id`))
mysql> delete from bm where id =2; Select * from bm where id = 2
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`book_manage`.`yg`, CONSTRAINT `yg_ibfk_1` FOREIGN KEY (`bm_id`) REFERENCES `bm` (`id`))
Copy the codeMany to many
The many-to-many relationship Between a book and an author is a many-to-many relationship.
Start with the list of books: Can a book have more than one author? And the answer is yes
From the perspective of the author list: Can an author write more than one book? And the answer is yes
Books and authors are two-way one-to-many so the relationship between these two tables is many-to-many.
Let’s start by creating two tables:
Create a table of books
create table book(
id int primary key auto_increment,
title varchar(32),
price int,
author_id int.foreign key(author_id) references author(id)
on update cascade
on delete cascade
);
Create author table
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
book_id int.foreign key(book_id) references book(id)
on update cascade
on delete cascade
);
Copy the codeIf we create a one-to-many table relationship, we must first create the associated table, that is, no foreign key table, but many-to-many relationship is two-way one-to-many, each table will have foreign key existence, how to do? The solution is to create a third table that is dedicated to storing the association of two tables for many-to-many relationships.
Create a table of books
mysql> create table book(
id int primary key auto_increment,
name varchar(10),
price int
);
Query OK, 0 rows affected (0.01 sec)
Create author table
mysql> create table author(
id int primary key auto_increment,
name varchar(6),
age int
);
Query OK, 0 rows affected (0.01 sec)
Create a chapter 3 table where the book and Author tables are associated
mysql> create table book2author(
id int primary key auto_increment,
author_id int,
book_id int.foreign key(author_id) references author(id)
on update cascade
on delete cascade,
foreign key(book_id) references book(id)
on update cascade
on delete cascade);
Query OK, 0 rows affected (0.02 sec)
Copy the codeOne to one
If a table field much more special, every time when querying data, not all fields are used to, we can watch a split in two, such as user information table, the user’s information includes the user name password users age gender address telephone and so on, may often use only the user’s user name and password, In this case, we can divide a user information table into user basic information table and user details table, and also judge the relationship between these two tables by perspectivethinking:
Let’s start with the basic user information table: Can a user have more than one detail? The answer is no;
Look at the user details table: Can a user detail belong to multiple users? The answer is no;
One-way one-to-many is not true, then the table relationship between the two is one-to-one or no relationship.
When the one-to-one foreign key relationship is established using SQL statements, the foreign key can be set on either side. However, it is recommended to set the foreign key on the table with high query frequency. Similarly, the associated table should be created before the table is created.
Create a user detail table
create table authordetail(
id int primary key auto_increment,
phone int,
addr varchar(64));- Basic user information table
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
authordetali_id int.foreign key(authordetali_id) references authordetali(id)
on update cascade
on delete cascade
);
Copy the codeSummary of table relationships
The establishment of a table relationship requires the use of a foreign key, and the relationship between tables is judged by perspective-taking.
One-to-many table relationships: Foreign keys are built on the many side
One-to-one table relationships: Foreign keys can be built on either side. It is recommended to build foreign keys on the side with high query frequency
Many-to-many table relationship: You need to create a third table to store the relationship between the two tables
conclusion
The article was first published on the wechat public account Program Yuan Xiaozhuang, and synchronized with nuggets and Zhihu.
The code word is not easy, reprint please explain the source, pass by the little friends of the lovely little finger point like and then go (╹▽╹)
