Relationships among multiple tables - one to many - many to many - one to one - foreign key constraints

Posted by foxy69 on Tue, 18 Jan 2022 15:47:16 +0100

11. Relationship between multiple tables - one to many - many to many - one to one - foreign key constraint

1. Table relationship overview

In real life, there must be a relationship between entities, such as husband and wife, departments and employees, users and orders, orders and commodities, students and courses, etc. When designing tables, we should reflect the relationship between tables! There are three types of relationships between tables:

  1. one-on-one (husband and wife)
  2. One to many (departments and employees, users and orders)
  3. Many to many (students and courses)

For example:

On the day of double 11, brother Ma and brother Dong placed some orders on Taobao respectively. It is known that brother Ma placed an order with a total amount of 999 yuan Dongge placed two orders with the order amount of 1314 yuan and 10 yuan respectively

Thinking: how should the database store this data?

Before relationship is established: the relationship between data cannot be known through table data, so it is meaningless to store data

image-20200529100830282

After the relationship is established:

Through the analysis of this business, we can know that a user can have multiple orders, and an order belongs to only one user

We call the side of 1 main table or 1 table We call multiple parties slave tables or multiple tables

We usually add a field on the other side to store the primary key value of the main table. We call this field foreign key field

The value of the foreign key field must be the value of the primary key of the main table. If it is other values, it has no meaning

image-20200529101003797

The constraint used to restrict the value of a foreign key field to the value of the primary key of the main table is called - foreign key constraint

2. One to many

One to many (1:n) for example, classes and students, departments and employees, customers and orders, classifications and commodities. One to many table building principle: create a field in the slave table (multi-party) to point to the primary key of the primary table (one party) We call this field a foreign key

3. Many to many

Many to many (m:n) for example, many to many relationships between teachers and students, students and courses, users and roles. Table building principle: you need to create a third table with at least two fields in the middle table, which are used as foreign keys to point to the primary key of each party.

4. One to one

One to one (1:1) is rarely used in practical development Because one-to-one can be created into a table. Two table building principles:

  • UNIQUE foreign key: the primary key of the master table and the foreign key (UNIQUE) of the slave table form a primary foreign key relationship. The foreign key is UNIQUE
  • A foreign key is a primary key: the primary key of the primary table and the primary key of the secondary table form a primary foreign key relationship

5. Foreign key constraints

5.1 what are foreign key constraints

A field in a table refers to the primary key of another table. Primary table: constrain others. Secondary table / slave table: use other people's data and be constrained by others

5.2 creating foreign keys

  1. Add foreign key when creating a new table: [CONSTRAINT] [foreign key CONSTRAINT name] foreign key (foreign key field name) REFERENCES primary table name (primary key field name) keyword explanation: CONSTRAINT -- CONSTRAINT keyword foreign key (foreign key field name) - - a field is used as a foreign key REFERENCES -- primary table name (primary key field name) refers to a field in the primary table
  2. Add a FOREIGN KEY to an existing table: ALTER TABLE adds [constraint] [FOREIGN KEY constraint name] FOREIGN KEY (FOREIGN KEY field name) REFERENCES from the table main table (primary key field name);

Specific operation:

Demonstrate with "add foreign key when creating a new table"

-- Create department table first
CREATE TABLE department (
 id INT PRIMARY KEY AUTO_INCREMENT,
 dep_name VARCHAR(20),
 dep_location VARCHAR(20)
);

-- Add 2 departments
INSERT INTO department (dep_name, dep_location) VALUES ('R & D department', 'Guangzhou'), ('Sales Department', 'Shenzhen');

-- Then create an employee table,Add foreign key constraint
CREATE TABLE employee (
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(20),
 age INT,
 dep_id INT,
 -- Add a foreign key
 -- Foreign key naming company requirements,commonly fk ending
 CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id)
);

The implementation is as follows:

-- Create department table first
mysql> CREATE TABLE department (
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> dep_name VARCHAR(20),
    -> dep_location VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

-- Add 2 departments
mysql> INSERT INTO department (dep_name, dep_location) VALUES ('R & D department', 'Guangzhou'), ('Sales Department', 'Shenzhen');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

-- View Department data
mysql> select * from department;
+----+-----------+--------------+
| id | dep_name  | dep_location |
+----+-----------+--------------+
|  1 | R & D department    | Guangzhou         |
|  2 | Sales Department    | Shenzhen         |
+----+-----------+--------------+
2 rows in set (0.00 sec)

-- Then create an employee table,Add foreign key constraint
mysql> CREATE TABLE employee (
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> NAME VARCHAR(20),
    -> age INT,
    -> dep_id INT,
    -> -- Add a foreign key
    -> -- Foreign key naming company requirements,commonly fk ending
    -> CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>

Add data normally

INSERT INTO employee (NAME, age, dep_id) VALUES
('Zhang San', 20, 1),
('Li Si', 21, 1),
('Wang Wu', 20, 1),
('Lao Wang', 20, 2),
('king', 22, 2),
('Xiao Wang', 18, 2);

The implementation is as follows:

-- Insert data into employee
mysql> INSERT INTO employee (NAME, age, dep_id) VALUES
    -> ('Zhang San', 20, 1),
    -> ('Li Si', 21, 1),
    -> ('Wang Wu', 20, 1),
    -> ('Lao Wang', 20, 2),
    -> ('king', 22, 2),
    -> ('Xiao Wang', 18, 2);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

-- see employee Table data
mysql> select * from employee;
+----+--------+------+--------+
| id | NAME   | age  | dep_id |
+----+--------+------+--------+
|  1 | Zhang San   |   20 |      1 |
|  2 | Li Si   |   21 |      1 |
|  3 | Wang Wu   |   20 |      1 |
|  4 | Lao Wang   |   20 |      2 |
|  5 | king   |   22 |      2 |
|  6 | Xiao Wang   |   18 |      2 |
+----+--------+------+--------+
6 rows in set (0.00 sec)

mysql>

Department error data addition failed

INSERT INTO employee (NAME, age, dep_id) VALUES ('Two kings', 20, 5);

The implementation is as follows:

mysql> select * from department;
+----+-----------+--------------+
| id | dep_name  | dep_location |
+----+-----------+--------------+
|  1 | R & D department    | Guangzhou         |
|  2 | Sales Department    | Shenzhen         |
+----+-----------+--------------+
2 rows in set (0.00 sec)

-- The inserted data foreign key can be found dep_id=5, and department Table does not dep_id=5 Data.
mysql> INSERT INTO employee (NAME, age, dep_id) VALUES ('Two kings', 20, 5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`employee`, CONSTRAINT `emp_depid_ref_dep_id_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))
mysql>

5.3 delete foreign keys (understand)

ALTER TABLE drop foreign key name from the table;

Specific operation:

  • Delete EMP of employee table_ depid_ ref_ dep_ id_ FK foreign key
ALTER TABLE employee DROP FOREIGN KEY emp_depid_ref_dep_id_fk;
  • Add a foreign key when the employee table exists
ALTER TABLE employee ADD CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id);

The implementation is as follows:

-- View current employee Table structure of
mysql> show create table employee;
+----------+-----------------------+
| Table    | Create Table          |
+----------+-----------------------+
| employee | CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `dep_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `emp_depid_ref_dep_id_fk` (`dep_id`),
  CONSTRAINT `emp_depid_ref_dep_id_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`) -- Foreign key
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+----------+-----------------------+
1 row in set (0.00 sec)

-- Delete foreign key emp_depid_ref_dep_id_fk
mysql> ALTER TABLE employee DROP FOREIGN KEY emp_depid_ref_dep_id_fk;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- View after deleting foreign keys employee
mysql> show create table employee;
+----------+------------------------+
| Table    | Create Table           |
+----------+------------------------+
| employee | CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `dep_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `emp_depid_ref_dep_id_fk` (`dep_id`) -- The foreign key has been deleted
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+----------+-------------------------+
1 row in set (0.00 sec)

-- stay employee Adding foreign keys when tables exist
mysql> ALTER TABLE employee ADD CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id);
Query OK, 6 rows affected (0.34 sec)
Records: 6  Duplicates: 0  Warnings: 0

-- View current employee structure
mysql> show create table employee;
+----------+----------------------+
| Table    | Create Table         |
+----------+----------------------+
| employee | CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `dep_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `emp_depid_ref_dep_id_fk` (`dep_id`),
  CONSTRAINT `emp_depid_ref_dep_id_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`) -- Re added foreign keys
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+----------+-----------------------+
1 row in set (0.00 sec)

mysql>

5.4 precautions for data operation

When adding data:
 Add the data in the main table first,Then add the data from the table
 When deleting data:
 Delete the data from the table first,Delete the data in the main table again
 When modifying data:
 If the primary key in the primary table is referenced by the secondary table,The value of this primary key cannot be modified

Hot Topics

©2025 Programmer Think