Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.3(EOL), 10.4(EOL), 10.7(EOL), 10.8(EOL)
-
None
Description
SQL-2016 says:
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:
CREATE TABLE t1 |
(
|
a INT, |
b INT CHECK (a<10) |
);
|
MariaDB allows this statement as an extension. But in some cases this can be a reason for bugs. See MDEV-27359.
Oracle
Oracle 21c does not allow this statement:
ORA-02438: Column check constraint cannot reference other columns
|
SQL Server
SQL Server 2019 does not allow this statement:
Msg 8141 Level 16 State 0 Line 1
|
Column CHECK constraint for column 'b' references another column, table 't1'.
|
Msg 1750 Level 16 State 0 Line 1
|
IBM DB2
DB2 Developer C 11.1 does not allow this statement:
[IBM][CLI Driver][DB2/LINUXX8664] SQL0548N A check constraint or generated column that is defined with "A" is invalid. SQLSTATE=42621 SQLCODE=-548
|
PostgreSQL
PostgreSQL allows this statement, but pg_dump shows that it actually moves the constraint to the table level, which makes it SQL compliant:
$ pg_dump -st t1
|
...
|
CREATE TABLE public.t1 ( |
a integer, |
b integer, |
CONSTRAINT t1_a_check CHECK ((a < 10)) |
);
|
...
|
However, PostgreSQL moves even correct column level constraints to the table level:
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 |
(
|
a INT CHECK (a<10), |
b INT |
);
|
$ pg_dump -st t1
|
...
|
CREATE TABLE public.t1 ( |
a integer, |
b integer, |
CONSTRAINT t1_a_check CHECK ((a < 10)) |
);
|
...
|
Possible solutions
- 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.
Attachments
Issue Links
- relates to
-
MDEV-27018 IF and COALESCE lose "json" property
- Closed
-
MDEV-27350 Complex CHECK constraint loses JSON property
- Open
-
MDEV-27358 Table level CHECK(JSON_VALID()) constraint loses JSON property
- Open
-
MDEV-27359 Crossed JSON_VALID constraint imposes JSON property to a wrong column
- Open