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
            elenst Elena Stepanova added a comment - - edited

            Please note that SQL-99 is not a MariaDB manual, it's the standard, of which MariaDB only implements a subset. Here is the documentation of the feature syntax in current MariaDB: https://mariadb.com/kb/en/mariadb/constraint/

            You can either have CHECK(expression) as a part of a column definition, or CONSTRAINT [constraint_name] CHECK (expression). The latter goes outside the column definition; the page doesn't say that, it should probably be added as an explicit note; it is indeed not implemented as a part of a column definition, which the example in the description is about. [NOT] DEFERRABLE is not implemented either.

            I've converted it into a task.

            elenst Elena Stepanova added a comment - - edited Please note that SQL-99 is not a MariaDB manual, it's the standard, of which MariaDB only implements a subset. Here is the documentation of the feature syntax in current MariaDB: https://mariadb.com/kb/en/mariadb/constraint/ You can either have CHECK(expression) as a part of a column definition, or CONSTRAINT [constraint_name] CHECK (expression) . The latter goes outside the column definition; the page doesn't say that, it should probably be added as an explicit note; it is indeed not implemented as a part of a column definition, which the example in the description is about. [NOT] DEFERRABLE is not implemented either. I've converted it into a task.
            anikitin Andrii Nikitin (Inactive) added a comment -

            I don't see any reason why "b INT CHECK (b>2)" is valid column definition but "b INT constraint bc CHECK (b>2)" is invalid , except that this path was overlooked during implementation of linked task; sorry that my description doesn't make explicit stress on that.

            anikitin Andrii Nikitin (Inactive) added a comment - I don't see any reason why "b INT CHECK (b>2)" is valid column definition but "b INT constraint bc CHECK (b>2)" is invalid , except that this path was overlooked during implementation of linked task; sorry that my description doesn't make explicit stress on that.
            elenst Elena Stepanova added a comment -

            I think the main reason is that there were no actual grammar changes here, the existing syntax is the legacy from the times where people cared less about complying with the standard. It's just the constraints were ignored earlier, and now they are actually used; but both variants that work now were grammatically acceptable before, e.g. you can do in previous versions

            CREATE TABLE t1 (b INT CHECK (b>2));

            or

            CREATE TABLE t1 (b INT, CONSTRAINT bc CHECK (b>2));

            it won't complain. But it will complain about

            CREATE TABLE t1 (b INT CONSTRAINT bc CHECK (b>2));

            elenst Elena Stepanova added a comment - I think the main reason is that there were no actual grammar changes here, the existing syntax is the legacy from the times where people cared less about complying with the standard. It's just the constraints were ignored earlier, and now they are actually used; but both variants that work now were grammatically acceptable before, e.g. you can do in previous versions CREATE TABLE t1 (b INT CHECK (b>2)); or CREATE TABLE t1 (b INT , CONSTRAINT bc CHECK (b>2)); it won't complain. But it will complain about CREATE TABLE t1 (b INT CONSTRAINT bc CHECK (b>2));
            anikitin Andrii Nikitin (Inactive) added a comment -

            I am not sure what are we discussing here - if there was any reason to not implement discussed variant of syntax during mentioned MDEV-7563 , it probably should be noted here or inside MDEV-7563, otherwise - we definitely should implement that.

            anikitin Andrii Nikitin (Inactive) added a comment - I am not sure what are we discussing here - if there was any reason to not implement discussed variant of syntax during mentioned MDEV-7563 , it probably should be noted here or inside MDEV-7563 , otherwise - we definitely should implement that.
            serg Sergei Golubchik added a comment -

            For the reference, the standard syntax is

            <column definition> ::=
            		 
            <column name> [ <data type or domain name> ]
            		 
            [ <default clause> | <identity column specification> | <generation clause>
            		 
            | <system time period start column specification>
            		 
            | <system time period end column specification> ]
            		 
            [ <column constraint definition>... ]
            		 
            [ <collate clause> ]
            		 
             
            		 
            <column constraint definition> ::=
            		 
            [ <constraint name definition> ] <column constraint> [ <constraint characteristics> ]
            		 
             
            		 
            <column constraint> ::=
            		 
            NOT NULL
            		 
            | <unique specification>
            		 
            | <references specification>
            		 
            | <check constraint definition>

            So "CONSTRAINT name" can be specified for any constraint, also for UNIQUE and NOT NULL, not only for CHECK

            serg Sergei Golubchik added a comment - For the reference, the standard syntax is <column definition> ::= <column name> [ <data type or domain name> ] [ <default clause> | <identity column specification> | <generation clause> | <system time period start column specification> | <system time period end column specification> ] [ <column constraint definition>... ] [ <collate clause> ]   <column constraint definition> ::= [ <constraint name definition> ] <column constraint> [ <constraint characteristics> ]   <column constraint> ::= NOT NULL | <unique specification> | <references specification> | <check constraint definition> So "CONSTRAINT name" can be specified for any constraint, also for UNIQUE and NOT NULL, not only for CHECK

            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.