09-mysql database----variants of foreign keys

This section focuses on:

• How to find the relationship between two tables
• Three types of relationships in the table

First, Introduction

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

Analysis Step: #1, stand in the angle of the left table to find out if the left table of multiple records can correspond to a record of the right table, if so, then prove a field of the left table foreign key right Table a field (usually ID) #2, and then stand in the angle of the right table to find whether the right table of multiple records can correspond to a record If it is, then prove a field in the right table foreign key to the left table a field (usually an ID) #3, Summary: #多对一: If only step 1 is established, then is the left table many to one right table if only step 2 is established, then the right table many to one left table # Many-to-many if steps 1 and 2 are also established, The two tables are proved to be one-way multi-pair, that is, many-to-many, you need to define a relational table of the two tables to specifically store the relationship between them # one-to-one: if 1 and 2 are not true, but a record of the left table only corresponds to a record of the right table, and vice versa. This is simple, that is, in the left table foreign key to the right table based on the left table's foreign key field is set to a unique can

Iii. three types of relationships in the table

(1) Books and publishing houses

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

　　关联方式：foreign key

CREATE TABLE press (ID int primary key auto_increment, name varchar); CREATE table book (id int primary key AU To_increment, name varchar, press_id int NOT NULL, constraint fk_book_press foreign key (press_id) refere NCES Press (ID) on DELETE cascade on UPDATE cascade) # First insert the record into the associated table inserts into presses (name) VALUES (' Beijing Industrial Mines Publishing House '), (' People's music does not Nice publishers '), (' Intellectual property is not used by publishers '); # Insert records into the associated table insert into book (name,press_id) VALUES (' Nine-Yang ', 1), (' Nine Yin Canon ', 2), (' Nine Yin Bones claw ', 2), (' Lone solitary nine Swords ') , 3), (' Dragon 10 Slap ', 2), (' Sunflower Treasure Book ', 3); query result:mysql> select * from book;+----+-----------------+----------+| ID | name |  press_id |+----+-----------------+----------+| 1 |        Nine Yang Martial |  1 | | 2 |        Nine Yin Scriptures |  2 | | 3 |        Nine Yin Bones Claw |  2 | | 4 |        Lone Nine Swords |  3 | | 5 |        Dragon 10 Spank |  2 | | 6 |        Sunflower Treasure Book | 3 |+----+-----------------+----------+6 rows in Set (0.00 sec) mysql> SELECT * from press;+----+---------------------- ----------+| ID | Name |+----+--------------------------------+| 1 |  Beijing industrial Mines Publishing House | | 2 |  People's music is not good listening publishing house | | 3 | Intellectual property is not used by publishers |+----+--------------------------------+3 rows in Set (0.00 sec)

Book and publishing house (many to one)

(2) The relationship between the author and the book

　　多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多。Look at the picture and talk.

　　关联方式：foreign key+一张新的表

# Create the associated table author table, before the Book table in a multi-to-one relationship has been created create TABLE author (id int primary key auto_increment, name varchar); #这张表就存放 The relationship between the author table and the book table, that is, the relationship between the query and the table can be found. 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 the DELETE cascade on update CASC ADE, primary KEY (author_id,book_id)); #插入四个作者, the ID in turn opens insert into author (' Egon '), (' Alex '), (' Wusir '), (' Yuanhao '); # each author of the representative of Egon: Nine Yang Martial, nine Yin Canon, nine yin Bones claw, solitary nine swords, descending dragon 10 slap, Sunflower Treasure book Alex: Nine Yang Martial, sunflower Treasure book Wusir: Lone solitary nine sword, Dragon 10 Slap, Sunflower Treasure Book Yuanhao: Nine Yang Martial Magic # Insert the appropriate data into the Author2book table insert into Author2book (author_id,book_id) values ((), ((), (1,3), (1,4), (1,5), (1,6), (2,1), ( 2,6), (3,4), (3,5), (3,6), (4,1); # Now you can check author2book corresponding author and book relationship 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 |+----+-----------+---------+12 rows in Set (0.00 sec)

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

(3) Users and blogs

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

　　关联方式：foreign key+unique

#例如: A user can only register one blog # Two tables: User table (username) and blog # Create user table created table user (    ID int primary key auto_increment,    name Varc Har (20)); # Creating a blog Table create TABLE blog (    ID int primary key auto_increment,    URL varchar),    user_id int unique,< C5/>constraint fk_user foreign KEY (user_id) references user (ID) on    delete cascade on    update cascade); Insert the records in the user table insert into user (name) VALUES (' Alex '), (' Wusir '), (' Egon '), (' Xiaoma '); # Insert a record of the blog entry 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;

Users and blogs (one-to-one)

09-mysql database----variants of foreign keys