Use of integrity constraints

To understand when an OceanBase database performs the checking of constraints can help you know which types of operations are allowed when constraints are present.

Define the emp table, as shown in the following figure. Define a self-referential constraint on the emp table to make the values in the mgr column dependent on those in the empno column. For simplicity, the following content uses the empno (employee_id) column and mgr (manager_id) column in the emp table as examples.

Insert the first data entry into the emp table. In this case, the mgr column cannot reference the existing values of the empno column because the table contains no data. You can insert the data entry by using the following methods:

• If the mgr column does not have a NOT NULL constraint, you can enter a null in the mgr column of the first row. Foreign keys allow nulls. Therefore, this row can be inserted into the table.

• Enter the same value in the empno and mgr columns of the first row. This indicates that OceanBase Database performs constraint checking after the statement is executed. To allow the same value to be entered in the parent key and foreign key of the first row, the system must first execute the statement to insert the row, and then check whether the value in the mgr column of the row matches a specific value in the empno column of the emp table.

• Execute a multi-row INSERT statement, such as an INSERT statement with a nested SELECT statement, to insert multiple rows that are mutually referenced. For example, the values of the empno column and the mgr column in the first row are 200 and 300, whereas the values of these two columns in the second row are 300 and 200.

This also indicates that OceanBase Database defers the constraint checking until the statement is executed. All data rows are inserted first and then checked one by one for constraint violations.

The following content provides another example on the preceding self-referential constraint. Assume that a company is sold and all employee numbers need to be increased by 5000 to be consistent with the employee numbers of the new company. Manager numbers are actually employee numbers. Therefore, the manager numbers also need to be increased by 5000. The following figure shows the emp table before the update. The table consists of the empno and mgr columns. The empno column contains the following three values: 210, 211, and 212. The mgr column contains the following two values: 210 and 211.

Execute the following SQL statement for the emp table:

UPDATE EMP
SET empno = empno + 5000,
mgr = mgr + 5000;

This statement can be executed even though the integrity constraint defined on the emp table requires that each value in the mgr column must match a value in the empno column. This is because constraint checking is performed after the statement is executed. The following figure shows that OceanBase Database performs constraint checking after the SQL statement is fully executed.

In the update process, each employee number is first increased by 5000. Then, each manager number is increased by 5000. In the first step, the value 210 in the empno column is updated to 5210. In the second step, the value 211 in the empno column is updated to 5211, and the value 210 in the mgr column is updated to 5210. In the third step, the value 212 in the empno column is updated to 5212, and the value 211 in the mgr column is updated to 5211. Finally, constraint checking is performed.

The preceding example shows the constraint checking mechanism for the INSERT and UPDATE statements. The same constraint checking mechanism applies to all types of DML statements, such as UPDATE, INSERT, and DELETE statements.