SQL: constraints checks order

In SQL we have three four major constraints - 'UNIQUE', 'FOREIGN KEY', 'CHECK' and 'NOT NULL'.

Also we have non-standard ON CONFLICT clause, which propose what kind of action SQL should do if error happens. ON CONFLICT clause can be ABORT, FAIL, ROLLBACK, IGNORE, REPLACE. Except for REPLACE option, constraint checking order doesn't matter, because ABORT, FAIL, ROLLBACK, IGNORE semantics doesn't affect space data. According to that, proper constraint checking order in INSERT/UPDATE statements is:

1. Check all NOT NULL constraints with ON CONFLICT REPLACE option. If user didn't specify DEFAULT value for that column, change error action to ABORT and check new value. If user specified DEFAULT value and conflict happened, replace conflicting value with a DEFAULT one.
2. Check all UNIQUE constraints with ON CONFLICT REPLACE clause. If there is a conflicting row, replace it with a new one.
3. Fire all BEFORE triggers.
4. Check all NOT NULL constraints. If there was a REPLACE error action for a particular column, change it to ABORT and check new values.
5. Check all CHECK constraints.
6. Check all UNIQUE constraints. During that iteration error action is ABORT for all constraints with ON CONFLICT REPLACE. All conflicting rows replacing happened on step 2, on that step we don't change anything in space.
7. Fire all AFTER triggers.
8. Prepare new tuples and finish insertion/update by calling Tarantool API.

---

Right now we have two categories of constraints. First ones are constraints which are guaranteed by Tarantool (usual UNIQUE constraints and constraints with ON CONFLICT ABORT clause) and second ones are constraints which are checked by SQL bytecode (NOT NULL constraints, all CHECK constraints, UNIQUE constraints with ON CONFLICT FAIL/ROLLBACK/REPLACE/IGNORE).

1. Run all BEFORE triggers.

2. Check all NOT NULL constraints. If error action is REPLACE, then replace conflicting value with a default one. If default was not set up before then new value would be NULL.

3. Check all CHECK constraints. ON REPLACE clause for that constraint category is forbidden, so order doesn't matter for us because all error action do not cause changes in data.

4. Check all UNIQUE constraints from 2nd category. Order is the same like in CREATE TABLE statement but it doesn't matter for us if error action is not REPLACE, because even in conflict case incoming data won't be affected. However, if error action is REPLACE, that kind of constraint will be delayed until other non-replace constraints from 2nd category will be verified. If you have multiple constraints with ON CONFLICT REPLACE, then all of them will be moved to the end. In that case checking order will be the same as in CREATE TABLE statement.

5. Execute insertion into Tarantool spaces and indexes, execute box_insert or box_replace (if you do INSERT operation, then box_insert will be called, if you execute REPLACE INTO ... - box_replace is our case).

6. Fire all AFTER triggers

7. Finish insertion/update operation.

CREATE TABLE t1(a PRIMARY KEY, e UNIQUE, b UNIQUE ON CONFLICT REPLACE, c UNIQUE ON CONFLICT FAIL, d UNIQUE ON CONFLICT REPLACE);

INSERT INTO t1 VALUES (1, 1, 1, 1);

Execution order in that case:

1. Fire all BEFORE triggers.
2. Check uniqueness for column 'c'.
3. Check uniqueness for column 'b', if constraint was violated then do replace.
4. Check uniqueness for column 'd', if constraint was violated then do replace.
5. Execute box_insert in Tarantool, which will perform uniqueness check for column 'e'.
6. Fire all AFTER triggers.
7. Commit transaction.