What are the relationships of mysql foreign keys?
What is the relationship between mysql foreign keys, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.
Many to one
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
)
# insert a record into the associated table first
Insert into press (name) values
(Beijing Industrial Mine Publishing House)
(people's Music is not good for Publishing House)
('intellectual property rights are not useful to publishers')
# insert a record into the associated table
Insert into book (name,press_id) values
('Jiuyang Shengong', 1)
(Jiuyin True Sutra, 2)
('Jiuyin White Bone claw', 2)
('Dugujiujian', 3)
('falling Dragon Ten slaps', 2)
(the Treasure Book of Sunflower, 3)
Query results:
Mysql > select * from book
+-- +
| | id | name | press_id | |
+-- +
| | 1 | Jiuyang Shengong | 1 |
| | 2 | Jiuyin Sutra | 2 |
| | 3 | Jiuyin White Bone claw | 2 |
| | 4 | Dugu Jiujian | 3 |
| | 5 | Lianglong ten slaps | 2 |
| | 6 | Sunflower Baodian | 3 |
+-- +
Rows in set (0.00 sec)
Mysql > select * from press
+-+-
| | id | name |
+-+-
| | 1 | Beijing Industrial Mine Publishing House |
| | 2 | people's Music is not good for Publishing House |
| | 3 | intellectual property rights are not used by publishers |
+-+-
Rows in set (0.00 sec)
Many to many
# create the associated table author table. The previous book table says that a many-to-one relationship has been created
Create table author (
Id int primary key auto_increment
Name varchar (20)
)
# this table stores the relationship between author table and book table, that is, you can query the relationship between them and look up 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 and id in turn
Insert into author (name) values ('egon'), (' alex'), ('wusir'), (' yuanhao')
# the representative work of each author
Egon: Jiuyang Shenggong, Jiuyin True Sutra, Jiuyin White Bone claw, Dugu Nine Sword, falling Dragon Ten slaps, Sunflower Treasure Dian
Alex: Jiuyang Shenggong, Sunflower Treasure Book
Wusir: solitary nine swords, falling dragon ten slaps, sunflower treasure book
Yuanhao: Jiuyang Shenggong
# insert the corresponding data in the author2book table
Insert into author2book (author_id,book_id) values
(1pc1)
(1pd2)
(1pr 3)
(1pc4)
(1par 5)
(1pc6)
(2pr 1)
(2pc6)
(3pr 4)
(3pr 5)
(3pd6)
(4par 1)
# now you can check the relationship between the author and the book corresponding to author2book
Mysql > 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)
one-for-one
# for example: a user can only register for 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 in the user table
Insert into user (name) values
('alex')
('wusir')
('egon')
('xiaoma')
# insert the record of the 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 the blog address of wusir
Select url from blog where user_id=2
Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.
