11.4 <column definition>
17) If a <column constraint definition> is specified,
d) If a <column constraint definition> is specified that contains a <check constraint definition> CCD,
then it is equivalent to the following <table constraint definition>:
CND CCD CA
Each column reference directly contained in the <search condition> shall reference column C.
Which should mean that a column CHECK constraint inside a <column definition> can refer only to THIS column. It cannot refer to OTHER columns.
It impies that this CREATE statement is not valid:
MariaDB allows this statement as an extension. But in some cases this can be a reason for bugs. See MDEV-27359.
Oracle 21c does not allow this statement:
SQL Server 2019 does not allow this statement:
DB2 Developer C 11.1 does not allow this statement:
PostgreSQL allows this statement, but pg_dump shows that it actually moves the constraint to the table level, which makes it SQL compliant:
However, PostgreSQL moves even correct column level constraints to the table level:
- Keep at as it. Fix MDEV-27359 by iterating throw ALL column CHECK constraints. This can be inefficient though.
- Forbid crossed column CHECK constraints. Return an error, like Oracle, SQL Server, DB2 do.
- Move ALL column CHECK constraints to the table level, like PostgreSQL does.
- Move only crossed column CHECK constraints to the table level.
In case if we decide to move crossed constraints from the column to the table level (#3 or #4), a warning or a note should probably be issued.