foreign key
Introduction
Foreign keys are used to check whether corresponding data exists in the reference table.
In relational databases, foreign key is a mechanism used to establish relationships between tables. It plays a role in ensuring data integrity, maintaining data consistency, improving query performance and normalizing data. It helps Establish a stable and reliable database system.
Business scene
Foreign key constraints are used to ensure data integrity between tables and to establish associations between tables. Foreign key constraints play an important role in various business scenarios such as e-commerce, social media, procurement management, school management systems, and retail industries, and can be configured according to specific needs.
Example
Suppose we have two tables: employees and salaries, which are used to store company employee information and salary information respectively.
The salaries table contains the field emp_no as a foreign key, pointing to the emp_no field in the employees table. A one-to-many relationship is established between them, that is, an employee can have multiple salary records.
Prepare table structure
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
);
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`),
CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ;
--Clear interfering data
DELETE FROM employees WHERE emp_no = 10011;
As you can see, in the above table structure, we define a foreign key emp_no in the salaries table and perform foreign key constraints with the emp_no field in the employees table.
CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
Now we deliberately add a new salary information to the employee whose job number is 10011. This employee does not exist (the above test SQL specifically uses a DELETE statement to ensure that this job number does not exist)
Example 1: Reference to non-existent data when inserting data
Request message
{
"jsonrpc": "2.0",
"method": "hrm.salaries.add",
"params": {
"empNo": "910011",
"salary": 3000,
"fromDate": "2023-09-01",
"toDate": "2023-09-30"
},
"id": "client-unique-request-id"
}
Response message
{
"jsonrpc": "2.0",
"error": {
"code": -32600,
"message": "checking if the foreign key exists: foreign key constraint 'salaries_ibfk_1' fail in 'emp_no'",
"data": {
"errorCode": "EU.Constraint.FK.NotFound",
"errorDetails": [
{
"constraint": "ForeignKeyNotFound",
"field": "empNo",
"fieldValue": "910011",
"location": "hrm.salaries",
"message": "foreign key not found in hrm.salaries.{emp_no salaries_ibfk_1 hrm employees emp_no RESTRICT CASCADE} 910011"
}
],
"errorMessage": "foreign key constraint 'salaries_ibfk_1' fail in 'emp_no'"
}
},
"id": "client-unique-request-id"
}
The system will automatically check whether emp_no exists in the employees table. If it is found that it does not exist, it will prompt that the foreign key constraint failed and refuse to insert the record.
Above example Tested on Luwak 1.12.9