Detailed explanation of three relationship examples of MySQL foreign keys

create table press(
  id int primary key auto_increment,
  name varchar(20)
);
create table book(
  id int primary key auto_increment,
  name varchar(20),
  press_id int not null,
     constraint fk_book_press foreign key(press_id) references press(id)
  on delete cascade
  on update cascade
);

# First insert records into the associated table insert into press(name) values
('Beijing Industrial Mine Press'),
('People's Music is Not Good to Listen to Publishing House'),
('Intellectual property is useless to publishers')
;
# Insert records into the associated table insert into book(name,press_id) values
('Nine Yang Magic',1),
('Nine Yin Manual',2),
('Nine Yin White Bone Claw',2),
('Dugu Jiujian',3),
('Ten Slaps to Subdue the Dragon', 2),
('Sunflower Collection',3)
;

mysql> select * from book;
+----+-----------------+----------+
| id | name | press_id |
+----+-----------------+----------+
| 1 | Nine Yang Magic Skill | 1 |
| 2 | Nine Yin Manual | 2 |
| 3 | Nine Yin Bone Claw | 2 |
| 4 | Dugu Jiujian | 3 |
| 5 | Ten Slaps of the Dragon | 2 |
| 6 | Sunflower Manual | 3 |
+----+-----------------+----------+
rows in set (0.00 sec)
mysql> select * from press;
+----+--------------------------------+
| id | name |
+----+--------------------------------+
| 1 | Beijing Industrial Mine Press |
| 2 | People's Music Publishing House |
| 3 | Intellectual property is useless |
+----+--------------------------------+
rows in set (0.00 sec)

# Create the associated table author table. The previous book table has been created in the many-to-one relationship. create table author(
  id int primary key auto_increment,
  name varchar(20)
);
#This table stores the relationship between the author table and the book table. To query the relationship between the two, just query this table. create table author2book(
  id int not null unique auto_increment,
  author_id int not null,
  book_id int not null,
  constraint fk_author foreign key(author_id) references author(id)
  on delete cascade
  on update cascade,
  constraint fk_book foreign key(book_id) references book(id)
  on delete cascade
  on update cascade,
  primary key(author_id,book_id)
);
#Insert four authors, with their IDs arranged in order insert into author(name) values('egon'),('alex'),('wusir'),('yuanhao');
# Representative works of each author egon: Nine Yang Magic Art, Nine Yin Manual, Nine Yin Bones Claw, Dugu Jiujian, Ten Palms of the Dragon Subduing, Sunflower Collection alex: Nine Yang Magic Art, Sunflower Collection wusir: Dugu Jiujian, Ten Palms of the Dragon Subduing, Sunflower Collection yuanhao: Nine Yang Magic Art # Insert corresponding data into author2book table insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;

# Now you can check the relationship between the author and the book corresponding to author2bookmysql> select * from author2book;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 1 | 5 |
| 6 | 1 | 6 |
| 7 | 2 | 1 |
| 8 | 2 | 6 |
| 9 | 3 | 4 |
| 10 | 3 | 5 |
| 11 | 3 | 6 |
| 12 | 4 | 1 |
+----+-----------+---------+
rows in set (0.00 sec)

#For example: A user can only register one blog #Two tables: user table (user) and blog table (blog)
# Create a user table create table user(
  id int primary key auto_increment,
  name varchar(20)
);
# Create a blog table create table blog(
  id int primary key auto_increment,
  url varchar(100),
  user_id int unique,
  constraint fk_user foreign key(user_id) references user(id)
  on delete cascade
  on update cascade
);
#Insert records into the user table insert into user(name) values
('alex'),
('wusir'),
('egon'),
('xiaoma')
;
#Insert records into blog table insert into blog(url,user_id) values
('http://www.cnblog/alex',1),
('http://www.cnblog/wusir',2),
('http://www.cnblog/egon',3),
('http://www.cnblog/xiaoma',4)
;
# Query wusir's blog address select url from blog where user_id=2;