Bug #95143 No support for CHECK constraints in Workbench
Submitted: 26 Apr 2019 6:30 Modified: 14 May 2019 7:32
Reporter: Georgi Sotirov Email Updates:
Status: Verified Impact on me:
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:8.0.16 OS:Any
Assigned to: CPU Architecture:Any

[26 Apr 2019 6:30] Georgi Sotirov
MySQL Workbench 8.0.16 doesn't provide any support for viewing, creation or modifying of CHECK constraints in Navigator and table editor although CHECK constraints were implemented in version 8.0.16 of the server.

How to repeat:
1. Open MySQL Workbench 8.0.16
2. Select a table in Navigator and expand the list
3. You see Columns, Indexes, Foreign Keys and Triggers, but no CHECK constraints
4. Right click on table and select Alter Table...
5. There is nothing where one could enter CHECK constraint

Suggested fix:
1. I would expect to see a CHECK constraints item in the expanded elements of a table in Navigator allowing review of already defined CHECK constraints.
2. I would expect to see a CHECK constraint tab in table editor or CHECK constraint definition entry on Columns tab to allow for creation and modifying of CHECK constraints.
[26 Apr 2019 6:31] Georgi Sotirov
No CHECK constraint item for tables in navigator

Attachment: wb_8016_navigator_no_check.png (image/png, text), 22.59 KiB.

[26 Apr 2019 6:31] Georgi Sotirov
No way to create or modify CHECK constarints in table editor

Attachment: wb_8016_table_no_check.png (image/png, text), 86.89 KiB.

[26 Apr 2019 6:41] MySQL Verification Team
Hello Georgi Sotirov,

Thank you for the report and feedback.
Verified as described with WB 8.0.16 on Win7(and connected to MySQL Server 8.0.16).

[29 Apr 2019 11:51] Georgi Sotirov
There is also an error in Query Tab (SQL editor) on a queries like:

ALTER TABLE emp DROP CHECK emp_chks; /* on DROP keyword */

and like:

  DROP COLUMN created,
  DROP CHECK emp_chks; /* on CHECK keyword */

which in the first case is:

"DROP" is not valid at this position for this server version, expecting : CHARSET, CHECKSUM, COMMENT, PARTITION, REMOVE, REPAIR, ...

and in the second it is:

"CHECK" is not valid at this position for this server version, expecting  an identifier

although both queries are syntactically correct and could be successfully executed (against server 8.0.16 of course).
[14 May 2019 7:32] Georgi Sotirov
I just made an additional observation with the example from the documentation (see  https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html). The following query:

2: (
3:   CHECK (c1 <> c2),
4:   c1 INT CHECK (c1 > 10),
5:   c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),
6:   c3 INT CHECK (c3 < 100),
7:   CONSTRAINT c1_nonzero CHECK (c1 <> 0),
8:   CHECK (c1 > c3)
9: );

is flagged with error on line 5 (i.d. that defines column constraint) stating:

"CONSTRAINT" is not a valid at this position, expecting : ')'

although the query is perfectly valid and accepted by the server. Please, fix as well.
[14 May 2019 7:39] MySQL Verification Team
Thank you for your feedback.

[14 May 2019 7:47] Georgi Sotirov
MySQL Workbench SQL Editor shows error on column check constraint

Attachment: wb_column_chk_error.png (image/png, text), 8.77 KiB.