Summary of MySQL foreign key constraints and table relationships

Foreign Key

As mentioned above, storing employee information in one table will greatly waste resources and there will be too much duplicate data. This problem is similar to writing all the codes in one py file. Therefore, we can split a table into different tables and establish associations between these different tables. To establish associations, we need to use foreign keys. Foreign keys are also a type of constraint.

How to determine table relationships

There are three types of relationships between tables: one-to-many, many-to-many, and one-to-one. So how do you determine the relationship between tables?

It is recommended to think from the other person's perspective when determining the relationship between tables. What does that mean? That is, consider it from the perspective of two tables, such as the relationship between the employee table and the department table:

First, let’s look at it from the perspective of the employee table: Can an employee in the employee table belong to multiple departments? The answer is no

From the perspective of the department table: Can a department in the department table have multiple employees? The answer is yes

Therefore, the employee table and the department table are one-way one-to-many, and the employee table and the department table are one-to-many relationships.

How to create table relationships

When establishing table relationships, the association between tables usually uses the primary key id as the association field.

One-to-many relationship - Employee table and Department table

There is no concept of many-to-one in MySQL relationships. One-to-many and many-to-one are both one-to-many. When creating a one-to-many table relationship, you need to follow these points:

First, the foreign key field is established on the multiple side, that is, the employee table

Second, when creating a table, you must first create the associated party, that is, the department table

Third, when entering data, you must first enter the data of the associated table, that is, the data of the department table.

Fourth, when different tables establish relationships, cascading updates and deletions are required, which can also be called synchronous updates and deletions. If cascading updates and deletions are not established, the associated data in the associated table cannot be deleted or the ID cannot be modified because the two tables are interrelated.

-- Create the associated table, department tablemysql> create table bm(
    id int primary key auto_increment, 
    bm_name varchar(10), 
    bm_desc char(64)
);
Query OK, 0 rows affected (0.01 sec)

mysql> desc bm;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| bm_name | varchar(10) | YES | | NULL | |
| bm_desc | char(64) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

-- Create the table where the foreign key is located, the employee tablemysql> create table yg(
    id int primary key auto_increment, 
    yg_name varchar(6), 
    bm_id int, 
    foreign key(bm_id) references bm(id) -- indicates that bm_id is a foreign key field, which is associated with the id field in the bm table on update cascade # cascade update on delete cascade # cascade delete);
Query OK, 0 rows affected (0.10 sec)

mysql> desc yg;
+---------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| yg_name | varchar(6) | YES | | NULL | |
| bm_id | int(11) | YES | MUL | NULL | |
+---------+------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)


-- Insert datamysql> insert into bm (bm_name, bm_desc) values ​​('python', 'Life is short'),('go', 'let us go');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from bm;
+----+---------+--------------+
| id | bm_name | bm_desc |
+----+---------+--------------+
| 1 | python | life is short |
| 2 | go | let us go |
+----+---------+--------------+
2 rows in set (0.00 sec)


mysql> insert into yg (yg_name, bm_id) values ​​('xu', 1), ('zhuang', 2), ('lili', 1);
Query OK, 3 rows affected (0.09 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from yg;
+----+---------+-------+
| id | yg_name | bm_id |
+----+---------+-------+
| 2 | xu | 1 |
| 3 | zhuang | 2 |
| 4 | lili | 1 |
+----+---------+-------+
3 rows in set (0.00 sec)

-- The data associated with the foreign key must exist in the associated table, otherwise an error will be reported~
mysql> insert into yg (yg_name, bm_id) values ​​('xu', 3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`book_manage`.`yg`, CONSTRAINT `yg_ibfk_1` FOREIGN KEY (`bm_id`) REFERENCES `bm` (`id`))

-- If cascading updates and deletes are not used, the following errors will occur. The following SQL statements will be introduced in the following articles. . .
mysql> update bm set id=5 where id=2; -- Change the record with id=2 in the bm table to id=5
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`book_manage`.`yg`, CONSTRAINT `yg_ibfk_1` FOREIGN KEY (`bm_id`) REFERENCES `bm` (`id`))

mysql> delete from bm where id = 2; -- Delete the record with id 2 in the bm table ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`book_manage`.`yg`, CONSTRAINT `yg_ibfk_1` FOREIGN KEY (`bm_id`) REFERENCES `bm` (`id`)) 

Many-to-Many

The many-to-many relationship is introduced in detail using books and authors as an example. Why do books and authors have a many-to-many relationship?

Let’s start from the perspective of the book table: Can a book have multiple authors? The answer is yes

From the perspective of the author table: Can an author write multiple books? The answer is yes

The book table and the author table are bidirectional one-to-many, so the relationship between the two tables is many-to-many.

Let's create two tables:

-- Create a book table create table book(
    id int primary key auto_increment,
    title varchar(32),
    price int,
    author_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade
);

-- Create the author table create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    book_id int,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);

If you create a table in the above way, it will definitely not be successful. When creating a one-to-many table relationship, we said that we should first create the associated table, that is, the table without a foreign key. However, a many-to-many relationship is a bidirectional one-to-many relationship, and there will be foreign keys in each table. What should I do? The solution is to create a third table, which is used to store the association between the two tables of many-to-many relationship.

-- Create a book tablemysql> create table book(
    id int primary key auto_increment, 
    name varchar(10), 
    price int
);
Query OK, 0 rows affected (0.01 sec)

-- Create the author tablemysql> create table author(
    id int primary key auto_increment, 
    name varchar(6), 
    age int
);
Query OK, 0 rows affected (0.01 sec)


-- Create the third chapter table to store the association between the book and author tablesmysql> create table book2author(
    id int primary key auto_increment, 
    author_id int, 
    book_id int, 
    foreign key(author_id) references author(id) 
    on update cascade 
    on delete cascade, 	
    foreign key(book_id) references book(id) 
    on update cascade 
    on delete cascade);
Query OK, 0 rows affected (0.02 sec)

One-to-one

If a table has a lot of fields, and not all of them can be used every time you query data, we can split the table into two. For example, in the user information table, the user's information includes username, password, age, gender, address, phone number, etc., and only the user's username and password may be frequently used. In this case, we can split a user information table into a user basic information table and a user detailed information table. Similarly, the relationship between the two tables can be determined by transposition thinking:

First, look at the user basic information table: Can a user have multiple detailed information? The answer is no;

Let’s look at the user details table again: Can one user detail belong to multiple users? The answer is no;

If the one-way one-to-many relationship does not hold, then the table relationship between the two is one-to-one or no relationship.

When using SQL statements to establish a one-to-one foreign key relationship, the foreign key can be built on either side, but it is recommended to build the foreign key in a table with a higher query frequency. Similarly, when creating a table, create the associated table first.

-- Create a user details table create table authordetail(
	id int primary key auto_increment,
	phone int,
	addr varchar(64)
);

--User basic information table create table author(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    authordetali_id int,
    foreign key(authordetali_id) references authordetali(id)
    on update cascade
    on delete cascade
);

Summary of table relationships

To establish table relationships, you need to use foreign keys, and determine the relationship between tables by thinking in another's shoes.

One-to-many table relationship: foreign key is built on the many side

One-to-one table relationship: The foreign key can be built on either side, but it is recommended to be built on the side with high query frequency.

Many-to-many table relationship: You need to create a third table to store the relationship between the two tables

The above is the detailed summary of MySQL foreign key constraints and table relationships. For more information about MySQL foreign key constraints and table relationships, please pay attention to other related articles on 123WORDPRESS.COM!