Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27360

Column level CHECK constraint is not SQL Standard compliant

    XMLWordPrintable

    Details

      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

      1. Keep at as it. Fix MDEV-27359 by iterating throw ALL column CHECK constraints. This can be inefficient though.
      2. Forbid crossed column CHECK constraints. Return an error, like Oracle, SQL Server, DB2 do.
      3. Move ALL column CHECK constraints to the table level, like PostgreSQL does.
      4. 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

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.