MySQL foreign key (FOREIGN KEY) usage case detailed explanation

Introduction: The disadvantages of storing all data in one table

1. The organizational structure of the table is complex and unclear
2. Waste of space
3. Very poor scalability

In order to solve the above problems, multiple tables are needed to store data.

There are three types of relationships between records in tables: one-to-many, many-to-many, and one-to-one.

To deal with the relationship between tables, FOREIGN KEY will be used.

Many-to-one relationship:

Routine for finding relationships between tables

Example: Employee table: emp table Department: dep table

Part 1:

1. First, from the perspective of table emp
2. Find out whether multiple records in the emp table correspond to one record in the dep table.
3. Meaning of Translation 2:
   Multiple records in the left table emp ==> multiple employees One record in the right table dep ==> one department Final translation result: Can multiple employees belong to one department?

If yes, you need to proceed with part 2

Part 2:

1. From the perspective of table dep
2. Find out whether multiple records in the dep table correspond to one record in the emp table
3. Meaning of Translation 2:
   Multiple records in the right table dep ==> multiple departments One record in the left table emp ==> one employee

Final translation result: Can multiple departments contain the same employee? If not, it can be determined that the relationship between emp and dep is only a one-way many-to-one. How to achieve it?
At this time, you can use the foreign key. Add a dep_id field to the emp table, which points to the id field of the dep table.

What effect will foreign key bring?

Constraint 1: When creating a table, you must first create the associated table dep before you can create the associated table emp

create table dep(
    id int primary key auto_increment,
    dep_name char(10),
    dep_comment char(60)
);
 
create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female') not null default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);

Constraint 2: When inserting records, you must first insert the associated table dep before inserting the associated table emp

insert into dep(dep_name,dep_comment) values
('Teaching Department', 'Tutoring students and teaching courses'),
('Public Relations Department', 'Handling Public Relations Crisis'),
('Technology Department', 'Development Projects, Research Technology');
 
insert into emp(name,gender,dep_id) values
('monicx0','male',1),
('monicx1','male',2),
('monicx2','male',1),
('monicx3','male',1),
('lili','female',3);

Constraint 3: Both updating and deleting need to take into account the relationship between the associated and the associated.

Solution:

1. Delete the associated table emp first, then delete the associated table dep, and prepare to rebuild

2. Reconstruction: Add new features, update synchronously, delete synchronously

create table dep(
    id int primary key auto_increment,
    dep_name char(10),
    dep_comment char(60)
);
 
create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female') not null default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
    on update cascade
    on delete cascade
);

Now modify it:

The result is:

Now delete it:

The result is:

Many-to-many relationships:

There is a bidirectional many-to-one relationship between the records of the two tables, which is called a many-to-many relationship.

How to achieve it?

Create a third table with a field foreign key, the id of the left table, and a field foreign key, the id of the right table.

create table author(
    id int primary key auto_increment,
    name char(16)
);
 
create table book(
    id int primary key auto_increment,
    bname char(16),
    price int
);
 
insert into author(name) values
('monicx1'),
('monicx2'),
('monicx3')
;
insert into book(bname,price) values
('Python from entry to the grave', 200),
('Liunx from entry to death', 400),
('Java from entry to the grave', 300),
('PHP from entry to the grave', 100)
;
#Create the third table:
create table author2book(
    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
);
 
insert into author2book(author_id,book_id) values
(1,3),
(1,4),
(2,2),
(2,4),
(3,1),
(3,2),

In a one-to-one relationship, a record in the left table uniquely corresponds to a record in the right table, and vice versa.

create table customer(
    id int primary key auto_increment,
    name char(20) not null,
    qq char(10) not null,
    phone char(16) not null
);
 
create table student(
    id int primary key auto_increment,
    class_name char(20) not null,
    customer_id int unique, #This field must be unique foreign key (customer_id) references customer (id) #At this time, the foreign key field must be guaranteed to be unique
    on delete cascade
    on update cascade
);

This is the end of this article about the detailed case of MySQL foreign key (FOREIGN KEY) usage. For more relevant MySQL foreign key (FOREIGN KEY) usage content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!