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

constraint name in column constraints

Details

    Description

      According to the sql standard the following is valid command:

      CREATE TABLE Table_1 ( 
        column_1 SMALLINT 
            CONSTRAINT constraint_1 CHECK(column_1<400), 
        column_2 CHAR(5));

      Oracle grammar examples indicate the same:

      CREATE TABLE employees_demo
      		 
          ( employee_id    NUMBER(6)
      		 
          , first_name     VARCHAR2(20)
      		 
          , last_name      VARCHAR2(25) 
               CONSTRAINT emp_last_name_nn_demo NOT NULL
      		 
          , email          VARCHAR2(25) 
               CONSTRAINT emp_email_nn_demo     NOT NULL
      		 
      ...

      But both 10.2.6 and 10.3.0 show syntax error:

      mysql> CREATE TABLE Table_1 ( 
          ->   column_1 SMALLINT 
          ->       CONSTRAINT constraint_1 CHECK(column_1<400), 
          ->   column_2 CHAR(5));
      		 
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT constraint_1 CHECK(column_1<400), 
        column_2 CHAR(5))' at line 3

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-7563 Support CHECK constraint as in (or close to) SQL Standard

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            anikitin Andrii Nikitin (Inactive) created issue -
            anikitin Andrii Nikitin (Inactive) made changes -
            Field Original Value New Value
            elenst Elena Stepanova made changes -
            Affects Version/s 10.3.0 [ 22127 ]
            Affects Version/s 10.2.6 [ 22527 ]
            Issue Type Bug [ 1 ] Task [ 3 ]
            elenst Elena Stepanova made changes -
            Fix Version/s 10.3 [ 22126 ]
            serg Sergei Golubchik made changes -
            Issue Type Task [ 3 ] Bug [ 1 ]
            serg Sergei Golubchik made changes -
            Affects Version/s 10.2 [ 14601 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            serg Sergei Golubchik made changes -
            Summary CHECK CONSTRAINT still needs one more grammar path constraint name in column constraints
            serg Sergei Golubchik made changes -
            Description According to [online manual|https://mariadb.com/kb/en/sql-99/constraint_type-check-constraint/] following is valid command:

            {code:sql}
            CREATE TABLE Table_1 (
              column_1 SMALLINT
                  CONSTRAINT constraint_1 CHECK(column_1<400) NOT DEFERRABLE,
              column_2 CHAR(5));
            {code}

            [Oracle grammar examples|http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7002.htm#i2062833] indicate the same:

            {code:sql}
            CREATE TABLE employees_demo
                ( employee_id NUMBER(6)
                , first_name VARCHAR2(20)
                , last_name VARCHAR2(25)
                     CONSTRAINT emp_last_name_nn_demo NOT NULL
                , email VARCHAR2(25)
                     CONSTRAINT emp_email_nn_demo NOT NULL
            ...
            {code}

            But both 10.2.6 and 10.3.0 show syntax error:

            {noformat}
            mysql> CREATE TABLE Table_1 (
                -> column_1 SMALLINT
                -> CONSTRAINT constraint_1 CHECK(column_1<400) NOT DEFERRABLE,
                -> column_2 CHAR(5));
            ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT constraint_1 CHECK(column_1<400) NOT DEFERRABLE,
              column_2 CHAR(5))' at line 3
            {noformat}
            		According to the sql standard the following is valid command:

            {code:sql}
            CREATE TABLE Table_1 (
              column_1 SMALLINT
                  CONSTRAINT constraint_1 CHECK(column_1<400),
              column_2 CHAR(5));
            {code}

            [Oracle grammar examples|http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7002.htm#i2062833] indicate the same:

            {code:sql}
            CREATE TABLE employees_demo
                ( employee_id NUMBER(6)
                , first_name VARCHAR2(20)
                , last_name VARCHAR2(25)
                     CONSTRAINT emp_last_name_nn_demo NOT NULL
                , email VARCHAR2(25)
                     CONSTRAINT emp_email_nn_demo NOT NULL
            ...
            {code}

            But both 10.2.6 and 10.3.0 show syntax error:

            {noformat}
            mysql> CREATE TABLE Table_1 (
                -> column_1 SMALLINT
                -> CONSTRAINT constraint_1 CHECK(column_1<400) NOT DEFERRABLE,
                -> column_2 CHAR(5));
            ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT constraint_1 CHECK(column_1<400) NOT DEFERRABLE,
              column_2 CHAR(5))' at line 3
            {noformat}
            serg Sergei Golubchik made changes -
            Description According to the sql standard the following is valid command:

            {code:sql}
            CREATE TABLE Table_1 (
              column_1 SMALLINT
                  CONSTRAINT constraint_1 CHECK(column_1<400),
              column_2 CHAR(5));
            {code}

            [Oracle grammar examples|http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7002.htm#i2062833] indicate the same:

            {code:sql}
            CREATE TABLE employees_demo
                ( employee_id NUMBER(6)
                , first_name VARCHAR2(20)
                , last_name VARCHAR2(25)
                     CONSTRAINT emp_last_name_nn_demo NOT NULL
                , email VARCHAR2(25)
                     CONSTRAINT emp_email_nn_demo NOT NULL
            ...
            {code}

            But both 10.2.6 and 10.3.0 show syntax error:

            {noformat}
            mysql> CREATE TABLE Table_1 (
                -> column_1 SMALLINT
                -> CONSTRAINT constraint_1 CHECK(column_1<400) NOT DEFERRABLE,
                -> column_2 CHAR(5));
            ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT constraint_1 CHECK(column_1<400) NOT DEFERRABLE,
              column_2 CHAR(5))' at line 3
            {noformat}
            		According to the sql standard the following is valid command:

            {code:sql}
            CREATE TABLE Table_1 (
              column_1 SMALLINT
                  CONSTRAINT constraint_1 CHECK(column_1<400),
              column_2 CHAR(5));
            {code}

            [Oracle grammar examples|http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7002.htm#i2062833] indicate the same:

            {code:sql}
            CREATE TABLE employees_demo
                ( employee_id NUMBER(6)
                , first_name VARCHAR2(20)
                , last_name VARCHAR2(25)
                     CONSTRAINT emp_last_name_nn_demo NOT NULL
                , email VARCHAR2(25)
                     CONSTRAINT emp_email_nn_demo NOT NULL
            ...
            {code}

            But both 10.2.6 and 10.3.0 show syntax error:

            {noformat}
            mysql> CREATE TABLE Table_1 (
                -> column_1 SMALLINT
                -> CONSTRAINT constraint_1 CHECK(column_1<400),
                -> column_2 CHAR(5));
            ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT constraint_1 CHECK(column_1<400) NOT DEFERRABLE,
              column_2 CHAR(5))' at line 3
            {noformat}
            serg Sergei Golubchik made changes -
            Description According to the sql standard the following is valid command:

            {code:sql}
            CREATE TABLE Table_1 (
              column_1 SMALLINT
                  CONSTRAINT constraint_1 CHECK(column_1<400),
              column_2 CHAR(5));
            {code}

            [Oracle grammar examples|http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7002.htm#i2062833] indicate the same:

            {code:sql}
            CREATE TABLE employees_demo
                ( employee_id NUMBER(6)
                , first_name VARCHAR2(20)
                , last_name VARCHAR2(25)
                     CONSTRAINT emp_last_name_nn_demo NOT NULL
                , email VARCHAR2(25)
                     CONSTRAINT emp_email_nn_demo NOT NULL
            ...
            {code}

            But both 10.2.6 and 10.3.0 show syntax error:

            {noformat}
            mysql> CREATE TABLE Table_1 (
                -> column_1 SMALLINT
                -> CONSTRAINT constraint_1 CHECK(column_1<400),
                -> column_2 CHAR(5));
            ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT constraint_1 CHECK(column_1<400) NOT DEFERRABLE,
              column_2 CHAR(5))' at line 3
            {noformat}
            		According to the sql standard the following is valid command:

            {code:sql}
            CREATE TABLE Table_1 (
              column_1 SMALLINT
                  CONSTRAINT constraint_1 CHECK(column_1<400),
              column_2 CHAR(5));
            {code}

            [Oracle grammar examples|http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7002.htm#i2062833] indicate the same:

            {code:sql}
            CREATE TABLE employees_demo
                ( employee_id NUMBER(6)
                , first_name VARCHAR2(20)
                , last_name VARCHAR2(25)
                     CONSTRAINT emp_last_name_nn_demo NOT NULL
                , email VARCHAR2(25)
                     CONSTRAINT emp_email_nn_demo NOT NULL
            ...
            {code}

            But both 10.2.6 and 10.3.0 show syntax error:

            {noformat}
            mysql> CREATE TABLE Table_1 (
                -> column_1 SMALLINT
                -> CONSTRAINT constraint_1 CHECK(column_1<400),
                -> column_2 CHAR(5));
            ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT constraint_1 CHECK(column_1<400),
              column_2 CHAR(5))' at line 3
            {noformat}
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Minor [ 4 ]
            elenst Elena Stepanova made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 80911 ] MariaDB v4 [ 143941 ]

            People

              Unassigned Unassigned
              anikitin Andrii Nikitin (Inactive)
              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.