Referential integrity in the database (Foreign Key)

Before encountering such a problem in the project, I give a simple example to illustrate that

For example, we have two tables, one table (department) stores information about the department, such as department ID, department name, and so on, another table is the employee table (staff), and the employee table must contain the department where each employee is located. The question is, if we delete a record from the department table at this time, what happens in the staff table?

To answer the above questions, let's review what referential integrity is.

We often want to ensure that the value on a given property set in a relationship also appears in the value of a specific property set for another relationship. This condition is called referential integrity (referential integrity)

Just as we can use the foreign key clause as part of the CREATE TABLE statement in SQL to get a reputation.

For example, in the staff table we can use foreign key (dep_name) References department to indicate that the department name specified in each employee group Dep_name must exist in the department relationship.

More generally, the set of properties for the relationship R1 and R2 are R1 and R2 respectively, and the main codes are K1 and K2 respectively. If the requirements of the R2 in any of the T2, there are R1 in the T1 of the ancestor of the T1. K1 = T2.α, we call the subset of the R2 α as the reference relationship R1 K1 in the outer Code (foreign key)

When we violate referential integrity constraints, the usual processing is to deny execution of an operation that causes integrity corruption (that is, the transaction that made the update operation is rolled back). However, in the foreign key clause, you can indicate that if the delete or update action on the referenced relationship violates the constraint, then the system must take some steps to restore the integrity constraint by modifying the Ganso in the reference relationship, rather than rejecting such an operation.

Take a look at the following example:

This is our department relationship.

CREATE TABLE department (dept_name varchar),  building varchar, primary key (department)

under normal circumstances, our staff relations

<pre name= "code" class= "SQL" >create table staff (ID varchar (),  name varchar (a), NOT null  dept_name varchar,  primary key  (ID),  foreign key (dept_name) Reference Department)

The following is a staff relationship for a specific update action

CREATE TABLE staff (ID varchar (), name varchar (+), not  null  dept_name varchar,  primary key  (ID ),  foreign key (dept_name) Reference Department on  DELETE cascade on  update cascade)

The deletion is not rejected by the system, but by the online deletion of the staff relationship, if the deletion of Ganso in department causes this referential integrity constraint to be violated because of an on delete cascade clause that is associated with the foreign code's reputation. That is, delete the ganso that references the deleted system. Similarly, on UPDATE Cascade Synchronizes the updates of the meta-ancestor in the reference relationship when it is updated. SQL also allows the FOREIGN key clause to indicate actions other than cascade, and if the constraint is violated, reference and null (with set null in place of Cascade), or set default ).

However, in general, we are accustomed to the usage is, is not allowed to delete. If you really want to delete, you can add a field in the referenced relationship to indicate that the current record has been deleted, which is also convenient for future queries and other related operations.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Referential integrity in the database (Foreign Key)