[MDEV-12890] constraint name in column constraints - Jira

Andrii Nikitin (Inactive) created issue - 2017-05-24 08:26

Andrii Nikitin (Inactive) made changes - 2017-05-24 08:26

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-7563~~ [ ~~MDEV-7563~~ ]

Elena Stepanova made changes - 2017-05-24 10:55

Affects Version/s	10.3.0 [ 22127 ]
Affects Version/s	10.2.6 [ 22527 ]
Issue Type	Bug [ 1 ]	Task [ 3 ]

Elena Stepanova added a comment - 2017-05-24 11:04 - 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.

Elena Stepanova added a comment - 2017-05-24 11:04 - 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.

Elena Stepanova made changes - 2017-05-24 11:04

Fix Version/s

10.3 [ 22126 ]

Andrii Nikitin (Inactive) added a comment - 2017-05-24 11:15

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.

Andrii Nikitin (Inactive) added a comment - 2017-05-24 11:15 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.

Elena Stepanova added a comment - 2017-05-24 11:39

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));

Elena Stepanova added a comment - 2017-05-24 11:39 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));

Andrii Nikitin (Inactive) added a comment - 2017-05-24 11:49

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.

Andrii Nikitin (Inactive) added a comment - 2017-05-24 11:49 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.

Sergei Golubchik made changes - 2018-02-14 15:56

Issue Type

Task [ 3 ]

Bug [ 1 ]

Sergei Golubchik made changes - 2018-02-14 15:56

Affects Version/s

10.2 [ 14601 ]

Sergei Golubchik made changes - 2018-02-14 15:56

Fix Version/s		10.2 [ 14601 ]
Fix Version/s	10.3 [ 22126 ]

Sergei Golubchik made changes - 2018-02-14 15:57

Summary

CHECK CONSTRAINT still needs one more grammar path

constraint name in column constraints

Sergei Golubchik made changes - 2018-02-14 15:58

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}

Sergei Golubchik made changes - 2018-02-14 15:58

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}

Sergei Golubchik made changes - 2018-02-14 15:58

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}

Sergei Golubchik added a comment - 2018-02-14 16:00

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

Sergei Golubchik added a comment - 2018-02-14 16:00 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

Sergei Golubchik made changes - 2018-02-14 16:00

Priority

Major [ 3 ]

Minor [ 4 ]

Elena Stepanova made changes - 2018-02-14 21:32

Status

Open [ 1 ]

Confirmed [ 10101 ]

Sergei Golubchik made changes - 2021-12-06 21:36

Workflow

MariaDB v3 [ 80911 ]

MariaDB v4 [ 143941 ]

MariaDB Server

constraint name in column constraints

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration