MySQL Bugs: #95143: No support for CHECK constraints in Workbench

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:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	8.0.16	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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.

No CHECK constraint item for tables in navigator

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

No way to create or modify CHECK constarints in table editor

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

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

Thanks,
Umesh

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:

ALTER TABLE emp
  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).

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:

1: CREATE TABLE t1
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.

Thank you for your feedback.

regards,
Umesh

MySQL Workbench SQL Editor shows error on column check constraint

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