What is referential integrity

Before studying foreign keys, we must first understand the concept of “referential integrity”.

Attribute values in the referenced relationship must be able to be found or nulled in the referenced relationship, otherwise it does not conform to the semantics of the database.

In the actual operation, such as updating, deleting, inserting data in a table, check whether the data operation on the table is correct by referring to the data in another table that is associated with each other, and reject the operation if the data operation is incorrect.

For example, there is a table of employees with the fields id, name, and department. There is also a department table with fields for department number, department name, and department location.

The demo_employee table is as follows

The table demo_DEPT is as follows

Each employee should have a subordinate department, and this department must have data in the department table. Or no department is NULL for the employee, for example, the employee has not been assigned to a department.

If an employee, Yin Hongliang, is recorded as belonging to aurora Light’s construction department, but the company has no such department, this represents a violation of referential integrity, instead, it is compliance with referential integrity.

MySQL > select * from foreign key

1. The MySQL FOREIGN KEY constraint is used to establish a link between data in two tables. A table can have one or more FOREIGN keys.

For example, if the employee table has several columns, such as name, age, gender, department, and native place, only the department column can be a foreign key, or both the department and native place columns can be foreign keys.

2. The foreign key of one table can be null; otherwise, the value of each foreign key must be equal to some value of the primary key in the other table.

   For example, the foreign key of the employee table must depend on the primary key of the department table. The relationship can be expressed as follows.

   Demo_employee (dept_id) foreign key -> dependencies -> demo_DEPT (dept_id) primary key

3. A foreign key is a field in a table that is not the primary key of the table but corresponds to the primary key of another table. After a foreign key is defined, it is not allowed to delete rows that have an associated relationship in another table.

   For example, if there are 10 employees in a department, it is not allowed to terminate the department without transferring the employees to other departments or laying them off.

4. The primary purpose of foreign keys is to maintain consistency and integrity of data. ,

Master table and slave table

1. The primary table is also called the parent table: for two tables that have an associated relationship, the table where the primary key is in the associated field is the primary table.

2. Slave tables are also called child tables: for two tables that have an associated relationship, the table in which the foreign key of the associated field is the slave table.

For example, the relationship between the employee table and the department table is that the employee table depends on the department table. Therefore, the department table is the master table, and the employee table is the slave table. It can be understood that there are multiple employees in one department.

How to select the foreign key constraint field

When defining a foreign key, follow the following rules:

1. The parent table must already exist in the database or be a table that is currently being created. In the latter case, the parent table and child table are the same table, such a table is called self-referential table, this structure is called self-referential integrity.

   In the following table of employees, there is a supervisor column, and the supervisor is also an employee, so you need to refer to your own table. This is self-referential integrity, also known as internal and external key of the table.

2. A primary key must be defined for the parent table.

3. Primary keys cannot contain null values, but null values are allowed in foreign keys. That is, the contents of a foreign key are correct as long as each non-null value of the foreign key appears in the specified primary key.

4. Specify the column name or a combination of column names after the table name of the parent table. This column or combination of columns must be the primary key or candidate key of the parent table.

5. The number of columns in the foreign key must be the same as the number of columns in the primary key of the parent table.

6. The columns in the foreign key must have the same data type as the corresponding columns in the primary key of the parent table.

How to set the foreign key

5.1 Setting Foreign key Constraints when creating tables

To create a FOREIGN KEY in the data table, use the FOREIGN KEY keyword. The syntax is as follows:

[CONSTRAINT < CONSTRAINT FOREIGN KEY >] [CONSTRAINT < CONSTRAINT FOREIGN KEY >] 1. Select * from table where REFERENCES < table name > 1.Copy the code

Among them:

< foreign key name > is the name of the defined foreign key constraint. The same table cannot have foreign keys with the same name.

The field name indicates the field column whose child table needs to add a foreign key constraint.

The primary table name is the name of the table on which the quilt table foreign key depends;

Primary key columns represent primary key columns or column combinations defined in the primary table.

Example 1, to show the foreign key relationships between tables, create a department table demo_DEPT in the DEMO_DB database. The table structure is shown in the following table.

mysql> CREATE TABLE demo_dept
    -> (
    -> id INT(11) PRIMARY KEY- >name VARCHAR(22) NOT NULL,
    -> location VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.37 sec)
Copy the code

Create table DEMO_EMPLOYEE, create a foreign key constraint on table DEMO_EMPLOYEE, and associate its key DEPt_ID with the primary key ID of table DEMO_DEPT as a foreign key.

mysql> CREATE TABLE demo_employee
    -> (
    -> id INT(11) PRIMARY KEY- >name VARCHAR(25),
    -> dept_id INT(11),
    -> salary FLOAT- >CONSTRAINT fk_emp_dept
    -> FOREIGN KEY(dept_id) REFERENCES demo_dept(id)
    -> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC demo_employee;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | | | name | varchar(25) | YES | | NULL | | | dept_id| int(11) | YES | MUL | NULL | | |  salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+
4 rows in set (1.33 sec)
Copy the code

After the above statement is successfully executed, a foreign key constraint named fk_EMP_DEPT is added to the demo_employee table with the foreign key name dept_id, which depends on the primary key ID of the primary table DEMO_DEPT.

ERROR 1005(HY000) : Can’t create table’database.tablename'(errno: ERROR 1005(HY000) : Can’t create table’database.tablename'(errno: 150).”

5.2 Adding a foreign key constraint when modifying a table

The syntax for adding a foreign key constraint when modifying a table is as follows:

ALTER TABLE< table name >ADD CONSTRAINT< index name >FOREIGN KEY(< name >)REFERENCES< table name >(< column name >);Copy the code

[Example 2] Alter table tb_EMPLOYEE and associate field DEPt_ID with primary key ID of DEMO_DEPT. The following table shows the SQL statement and run result.

mysql> ALTER TABLE tb_employee
    -> ADD CONSTRAINT fk_demo_dept
    -> FOREIGN KEY(dept_id)
    -> REFERENCES demo_dept(id);
Query OK, 0 rows affected (1.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tb_employee\G
*************************** 1. row* * * * * * * * * * * * * * * * * * * * * * * * * * *Table: tb_employee
Create Table: CREATE TABLE `tb_employee` (
  `id` int(11) NOT NULL.`name` varchar(30) DEFAULT NULL.`dept_id` int(11) DEFAULT NULL.`salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_demo_dept` (`dept_id`),
  CONSTRAINT `fk_demo_dept` FOREIGN KEY (`dept_id`) REFERENCES `demo_dept` (`id`))ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.12 sec)
Copy the code

5.3 Deleting Foreign Key Constraints

Foreign keys defined in the database can be removed if they are no longer needed. MySQL > delete a foreign key from a table; delete a foreign key from a table; delete a foreign key from a table;

ALTER TABLE < TABLE name > DROP FOREIGN KEY < constraint name >;

Example 3, delete the foreign key constraint fk_DEMO_DEPT in table TB_EMPLOYEE, write the SQL statement and execute the result as shown below.

mysql> ALTER TABLE tb_employee
    -> DROP FOREIGN KEY fk_demo_dept;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tb_employee\G
*************************** 1. row* * * * * * * * * * * * * * * * * * * * * * * * * * *Table: tb_employee
Create Table: CREATE TABLE `tb_employee` (
  `id` int(11) NOT NULL.`name` varchar(30) DEFAULT NULL.`dept_id` int(11) DEFAULT NULL.`salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_demo_dept` (`dept_id`))ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)
Copy the code

The FOREIGN KEY constraint fk_EMP_DEPT is deleted successfully.

MySQL is one of the most correct databases used in daily work, so it is necessary to have a more in-depth and comprehensive grasp of distributed transactions, various database locks, propagation mechanisms and so on for high-level personnel.