Does the SQL standard define the order in which constraints are checked and start a run?

Question

I am curious if I can rely on any specific order of checking NOT NULL, FOREIGN KEY, UNIQUE, CHECK constraints and BEFORE triggers.

From experience, I know that MySQL first checks NOT NULL , then fires a BEFORE trigger, and then checks for UNIQUE . Oracle checks NOT NULL after a BEFORE trigger (I believe SQLServer does the same, but doesn't remember). Does the standard say anything about the order or all the way to the database provider?

Answer 1

This behavior seems to be a bug in MySQL and only affects BEFORE INSERT triggers, and BEFORE UPDATE triggers behave correctly.

the standard (as indicated in the comments on the question), of course, does not speak explicitly about it, but it is definitely implied:

For each state change of SCi, j in TECi, BEFORE triggers activated by SCi, j are executed before any of their trigger events take effect. When these trigger events take effect, any AFTER triggers are activated, TECi state changes are performed.

Error A NOT NULL must be part of an INSERT or UPDATE (i.e., an initiating event). The standard does not need to indicate this. It makes absolutely no sense to proactively check restrictions on a set of changes that is not final, because your BEFORE trigger is capable of both resolving errors and introducing new ones.

SUMMARY: It really does not depend on the database provider, because constraint checking after a trigger is always required.