Variant of the foreign key three kinds of relations

Because of the constraints of foreign key, the two tables form three kinds of relationships:

• Many-to-one
• Many-to-many
• One

Second, focus on understanding if you find the relationship between two tables

1) Book and publishing house

One-to-many (or many-to-one): A publishing house can publish multiple books. Look at the picture and talk.

　　关联方式：foreign key

#Create the associated table author table, before the Book table in the talk of a many-to-one relationship has been createdCREATE table author (id int primary key auto_increment, name varchar (20));#This table holds the relationship between the author table and the book table, that is, to look up the relationship between the two.CREATE TABLE Author2book (ID int notnull unique auto_increment, author_id int notnull, book_id int notNULL, Constraint Fk_author foreign key (author_id) references author (id) on the DELETE cascade on UPDATE cascade, Constraint Fk_book foreign KEY (book_id) references book (ID) on the DELETE cascade on UPDATE CASCADE, PRIMARY KEY ( author_id,book_id));#Insert four authors, id rowInsert into author (name) VALUES ('Xiao Ming'),('Heshun'),('Zhang San'),('John Doe');#Each author's masterpieceXiao Ming: Nine Yang Martial, nine Yin Canon, nine yin Bones claw, solitary nine swords, dragon 10 Slap, Sunflower Treasure Book Heshun: Nine Yang Martial, Sunflower Treasure book Zhang San: Lone solitary nine sword, Dragon 10 Slap, Sunflower Treasure Book John Doe: nine Yang Martial#Insert the appropriate data in the Author2book tableINSERT INTO Author2book (author_id,book_id) VALUES (The),(The),(1,3),(1,4),(1,5),(1,6),(2,1),(2,6),(3,4),(3,5),(3,6),(4,1);#Now we can check the relationship between the author and the book Author2book .Mysql> SELECT * fromAuthor2book;+----+-----------+---------+| 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 |+----+-----------+---------+12 rowsinchSet (0.00 sec)

relationship between author and book (Many-to-many)

(3) Users and blogs

　　一对一：一个用户只能注册一个博客，即一对一的关系。看图说话

　　关联方式：foreign key+unique

#For example: A user can only register a blog#Two tables: User tables and blogs (blog)#Create a user tableCREATE TABLE user (ID int primary key auto_increment, name varchar (20));#Create a blog tableCREATE 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 Date cascade);#insert a record in a user tableINSERT into user (name) VALUES ('Alex'),('Wusir'),('Egon'),('Xiaoma');#insert a record of a blog tableINSERT 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 addressSelect URL fromBlog where user_id=2;

Variant of the foreign key three kinds of relations