Foreign key constraints (foreign key) [MySQL][Database]

Updated to 2 days ago

Foreign key constraints (FOREIGN KEY (field) REFERENCES primary table name (field))

The role of foreign key constraints:

Qualify the reference integrity of a field

We mentioned reference integrity before when we talked about data integrity
- Quote integrity (eg: The department where the employee is located, you must be able to find this department in the department table)

Master and slave tables:

The main table is also called the father table, and the subordinate table is also called the child table.

Primary table (parent table): the referenced table
From table (subtable): Quoting someone else's table
How do we distinguish between slave table and primary table?
- We just need to remember: we specify the foreign key constraint in the "slave table", then we can know that the table is the slave table, and we also know which table is the master table

Features of foreign key constraints:

The foreign key column from the table must refer to the primary key column of the main table or the unique constraint column of the column.
- Because the value being referenced (that is, the value being referenced) must be unique
When creating a foreign key constraint, if the foreign key constraint is not named, the default foreign key constraint name is not a column name, but a foreign key name that is automatically generated (for example: student_ibfk_1;). Of course, we can also name the foreign key constraint.
If you specify foreign key constraints when creating a table, you must first create the master table and then create the slave table
- Because if we create foreign key constraints when creating the table, if we directly create the slave table, then since we have not created the main table yet, we have to refer to the main table in the slave table, of course, there will be an error in the execution.
When deleting a table, you must first delete the slave table (sub-table) and then delete the main table (parent table)
- Or delete the foreign key constraint first, and the constraint is gone, so it doesn't matter how you delete it.
When the records in the main table are referenced by the slave table, the records in the main table will not be allowed to be deleted. If we want to delete the data, we must first delete the data that depends on the record in the slave table, and then we can delete the data in the main table.
When a foreign key constraint is specified "from table", and there can be multiple foreign key constraints in a table
The foreign key column in the derived table may be different from the column names of the referenced columns in the main table, but the data types must be the same and the logical meaning is the same. If the types are different, an error will occur when creating the subtable.
When creating a foreign key constraint, the system will create a corresponding normal index on the column by default. The index name is the constraint name of the foreign key
After deleting foreign key constraints, the corresponding normal index must be deleted manually.

So how do we add foreign key constraints?

We still divide the foreign key constraints into two ways

Method 1: Add constraints when CREATE TABLE

Note: When we add foreign key constraints, we usually set foreign key constraints to table-level constraints, because when adding constraints with column-level constraints, we cannot specify the constraint name. For foreign key constraints, the default constraint name is not a column name. Since we need to specify the foreign key constraint name when deleting foreign key constraints, because multiple foreign key constraints can be declared in one table, we need to delete foreign key constraints through foreign key constraint names, so we should specify the foreign key constraint name when creating foreign key constraints.

Here we give an example to illustrate how to add constraints when CREATE TABLE

Let's create the main table first: (as follows)

CREATE TABLE dept1(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(15)
);

Here we create the main table (department table)

Then here we create the table: (as follows)

CREATE TABLE emp1(
 emp_id INT PRIMARY KEY AUTO_INCREMENT,
 emp_name VARCHAR(15),
 department_id INT,

 #Table level foreign key constraints
 CONSTRAINT fk_emp1_dept_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id)
 );

Here we create the From Table (Employee Table)
Here, a foreign key connection is established using the department_id field in the employee table and the dept_id field in the department table. At this time, the department table is the main table, and the employee table is the slave table
- At this time, please note: The fields referenced in the main table are key columns (that is, there are unique constraints or primary key constraints)
Here, when we add foreign key constraints, we pass the table-level constraint method, and use the constraint keyword to name the foreign key constraint as fk_emp1_dept_id
The purpose of the references keyword is to indicate the referenced columns in the main table

So what does it do after foreign key constraints?

Here we will give examples

INSERT INTO emp1
VALUES(1001,'tom',10);

At this time, the addition will fail because at this time, our main table (department table) has not yet added a department, so adding data from the table will not be added - because the function of our foreign key constraint is to judge that the value of the foreign key constraint field in the data added from the table must be found in the same value of the referenced field in the main table, otherwise it will be judged that the addition is not successful.

So if we want to add data in the slave table (employee table), we must first add data in the main table (department table). First, we must have a department so that employees of this department can have employees in this department first.

INSERT INTO dept1
VALUES(10,'IT');

At this time, we added a record in the department table (main table), that is, we added department No. 10. Then we can add data in the employee table later, but please note that the id of the employee department to be added must be 10.

At this time, we will perform the operation of adding employees again

INSERT INTO emp1
VALUES(1001,'tom',10);

Then at this time we have employees of department No. 10 from the table (employee table), so we cannot delete the department with id 10 in the main table.

DELETE FROM dept1
WHERE dept_id = 10;

At this time, the deletion will fail because we have employees from Department No. 10 from the table. At this time, we cannot delete department No. 10 in the main table. Unless the employees from Department No. 10 from the table are first deleted, or the foreign key constraints are deleted.

Method 2: Add foreign key constraints when ALTER TABLE

First we create two tables

Here we first create a department table (main table)

CREATE TABLE dept2(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(15)
);

Then here we create another employee table (from the table)

CREATE TABLE emp2(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT
);

Then here we add foreign key constraints when ALTER TABLE

ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY(department_id) REFERENCES dept2(dept_id);

Here we use the department_id field in the employee table and the dept_id in the department table to establish a foreign key connection in the employee table (the dept_id in our department table is a primary key column (that is, the primary key constraint is added)), so the addition of the foreign key constraint will be successful here.

After establishing a foreign key constraint, we can query whether the foreign key constraint is successfully added through a specified SQL statement.

SELECT *
FROM information_schema.`TABLE_CONSTRAINTS`
WHERE table_name = 'emp2';

Note: Here where table_name = '' in 'emp2' is a pair of single quotes, indicating that it is a string, that is, the table name. In order to judge the table name, we obviously need to judge the strings, rather than adding emphasis and keywords to distinguish them.

We have to add one more thing: Foreign key constraint level

Cascade: When update/delete records on the parent table, matched records in the synchronized update/delete child table
Set null : When update/delete records on the parent table, set the column matching records on the child table to null, but be careful that the foreign key column of the child table cannot be set to not null
- If the foreign key column in the sub-table is set to not null, then we cannot use the Set null method, because it is conflicting at this time. After we set Set null, if we want to modify or delete records in the main table, we will set the foreign key column of the corresponding record in the slave table to null. However, at this time, our foreign key column in the slave table is set to not null, so it is definitely not possible. Therefore, when the foreign key constraint level is Set null, the foreign key column in the slave table cannot be set to not null at this time.
No action: If there are records matching the primary table in the child table, update/delete operations on the primary table are not allowed.
Restrict: It's the same as No action
Set default: We just need to know this, not use it

Note: If the foreign key constraint level is not specified, it is equivalent to the Restrict method (or No action method)

The functions of Restrict and No action are the same

Here we use an example to understand if a constraint level is specified for foreign key constraints

eg: Here we set it to update on delete setnull

Let's create the main table (department table) first.

CREATE TABLE dept3(
d_id INT PRIMARY KEY,
d_name VARCHAR(50)
);

Then immediately create the From Table (Employee Table)

CREATE TABLE emp3(
e_id INT PRIMARY KEY,
e_name VARCHAR(15),
dept_id INT ,
CONSTRAINT fk_emp3_dept_id FOREIGN KEY(dept_id) REFERENCES dept3(d_id) ON UPDATE CASCADE ON DELETE SET NULL
);

Here we use table-level constraints to establish foreign key constraints for the dept_id field in the slave table and the d_id field in the main table, and set the foreign key constraint level to: on update cascade on delete set null, that is, for the update operation, our foreign key constraint level is cascade (that is, synchronous modification), and for the delete operation, our foreign key constraint level is set null (that is, after the main table is deleted, the corresponding field of the slave table is set to null)
Here we can find that our foreign key constraint level is given when setting foreign key constraints, through the method of: on update constraint level on delete constraint level

Note: For foreign key constraints, we should use the ON UPDATE CASCADE ON DELETE RESTRIT method (that is, modify the main table, then the slave table will be modified accordingly, but it is not allowed to delete the fields in the main table that are bound by foreign keys. When we correspond to the data from the table again)

So how do we remove foreign key constraints?

After deleting the foreign key constraint, we need to manually delete the normal index corresponding to the foreign key constraint.

Let's delete the foreign key constraint first

ALTER TABLE emp1
DROP FOREIGN KEY fk_emp1_dept_id;

Here we have deleted the foreign key constraint named fk_emp_dept_id in our emp1 table

After deleting the foreign key constraint, we need to delete the normal index corresponding to the foreign key constraint

ALTER TABLE emp1
DROP INDEX fk_emp1_dept_id;

Note: The index name of the normal index corresponding to the foreign key constraint is the same as the foreign key constraint name.