Example Analysis of mysql Foreign key
This article will explain in detail the example analysis of mysql foreign keys. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.
The details are as follows:
Because of the constraints of foreign key, there are three relationships between the two tables:
Many to one
Many to many
one-for-one
One to many or many to one
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) # first insert records insert into press (name) values ('Beijing Industrial Mine Publishing House'), ('people's Music is not good to listen to Press'), ('intellectual property rights are not used by publishing houses') into the related table. # then insert records 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), ('Sunflower Treasure Dian', 3)
Query results:
Mysql > select * from book +-+ | id | name | press_id | +-+ | 1 | Jiuyang Shengong | 1 | 2 | Jiuyin True Meridian | 2 | 3 | Jiuyin White Bone claw | 2 | 4 | Dugujiu Sword | 3 | | 5 | falling Dragon 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 to listen to Press | | 3 | Intellectual property rights are not used by publishers | +-+ rows in set (0.00 sec)
Many-to-many, introduce the third table
Many to many
# create the associated table author table. The previous book table has created create table author (id int primary key auto_increment, name varchar (20)) in a many-to-one relationship. # this table stores the relationship between the author table and the book table, that is, you can query the relationship between the two and look up the 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, id ranked 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, Dugujiu Jiujian, Jianglong Ten slaps, Sunflower Treasure Book alex: Jiuyang Shengong, Sunflower Treasure Book wusir: Dugu Jiujian, Jianglong Ten slap, Sunflower Treasure Book yuanhao: Jiuyang Shengong # insert the corresponding data insert into author2book (author_id,book_id) values (1line 1), (1line 2), (1line 3), (1line 4), (1minute 5), (1line 6) in the author2book table (2) (2), (2), (3), (4), (3), (6), (4) # 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 | 2 | 2 | 3 | 3 | 4 | 4 | 5 | 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) |
An one-on-one situation
one-for-one
# for example: a user can only register for one blog # two tables: user table (user) and blog table (blog) # create user table create table user (id int primary key auto_increment, name varchar (20)); # create 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 records in 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 wusir's blog address select url from blog where user_id=2; on "sample Analysis of mysql Foreign Keys". I hope the above content can be helpful to you so that you can learn more knowledge. if you think the article is good, please share it for more people to see.
