Knowing when OceanBase Database checks constraints helps identify the types of operations that can be performed when various constraints exist. The following examples illustrate when OceanBase Database checks constraints.
In the following figure, the EMP table is created. A self-referential constraint is defined on the EMP table so that the values of the MGR column depend on the values of the EMPNO column. For simplicity, the following content addresses only the EMPNO (employee_id) and MGR (manager_id) columns of the EMP table.
This example concerns the insertion of the first row into the EMP table. The value in the MGR column cannot reference any existing value in the EMPNO column because no data exists in the table. In this case, you can insert a row as described in the following scenarios:
If no
NOT NULLconstraint is defined on theMGRcolumn, you can insert a null value into theMGRcolumn in the first row. This row can be inserted into the table becauseFOREIGN KEYconstraints allow null values.You can insert the same values into the
EMPNOandMGRcolumns in the first row. In this case, OceanBase Database checks constraints after the statement is executed. To insert the first row with the same value in the parent key and the foreign key, OceanBase Database must first execute theINSERTstatement to insert the first row and then check whether any row in the table has anEMPNOcolumn value that corresponds to theMGRcolumn value of the first row.Execute a multiple row
INSERTstatement, such as anINSERTstatement with nestedSELECTstatements to insert multiple rows that reference one another. For example, the first row may have 200 for theEMPNOcolumn and 300 for theMGRcolumn, whereas the second row may have 300 for theEMPNOcolumn and 200 for theMGRcolumn.
In this case, constraint checking is deferred until the complete execution of the INSERT statement. OceanBase Database first inserts all rows and then checks for constraint violations row by row.
The following example illustrates when OceanBase Database checks a self-referential constraint for an UPDATE statement. Assume that the company is acquired and all employee numbers must be increased by 5000 to be consistent with the employee numbers of the new company. Manager numbers must also be increased by 5000 because they are also employee numbers. The following figure shows the EMP table before the update. This table contains the EMPNO and MGR columns. The EMPNO column has three values: 210, 211, and 212. The MGR column has two values: 210 and 211.
Execute the following SQL statement on the EMP table:
UPDATE EMP
SET empno = empno + 5000,
mgr = mgr + 5000;
Although the constraint defined on the EMP table requires that each MGR column value must correspond to an EMPNO column value, the preceding statement can still be executed because OceanBase Database checks the constraint after the statement is executed. The following figure shows that OceanBase Database checks the constraint after all operations of the SQL statement are performed.
First, 5000 is added to each employee number and then each manager number. In Step 1, 210 in the EMPNO column is updated to 5210. In Step 2, 211 in the EMPNO column is updated to 5211, and 210 in the MGR column is updated to 5210. In Step 3, 212 in the EMPNO column is updated to 5212, and 211 in the MGR column is updated to 5211. Finally, OceanBase Database checks the constraint.
First, 5000 is added to each employee number and then each manager number. In Step 1, 210 in the EMPNO column is updated to 5210. In Step 2, 211 in the EMPNO column is updated to 5211, and 210 in the MGR column is updated to 5210. In Step 3, 212 in the EMPNO column is updated to 5212, and 211 in the MGR column is updated to 5211. Finally, OceanBase Database checks the constraint.
The examples in this section describe the constraint checking mechanism for INSERT and UPDATE statements. In fact, the constraint checking mechanism is the same for all types of DML statements, including UPDATE, INSERT, and DELETE statements.