View/Edit support for CHECK constraints

[ibennetch]

Is your feature request related to a problem? Please describe.

MariaDB (starting with 10.2.1) and MySQL (starting with 8.0.16) have CHECK constraints, an integrity feature that is part of the CREATE or ALTER table statement.

Describe the solution you'd like

I think this belongs on the table structure tab, probably between Index and Partition. We should show these constraints, which can be found with either an SQL query against information_schema or through SHOW CREATE TABLE...

select constraint_schema as table_schema, table_name, constraint_name, check_clause as definition from information_schema.check_constraints order by table_schema, table_name

We can also allow people to create these constraints directly as part of the interface.

Describe alternatives you've considered

Additional context

• phpMyAdmin version: master

Add any other context or screenshots about the feature request here.

• https://mysqlserverteam.com/mysql-8-0-16-introducing-check-constraint/
• https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html
• https://mariadb.com/kb/en/constraint/#check-constraints

[DavidHopkinsFbr]

Merge this info with #13592 from 2017?

[mokraemer]

at least changing a column, should not change/drop a given constraint!

[nunoperalta]

Hey!

I came here to suggest exactly this.

1. Add a CHECK section in the page where we add a new column to a table (similar to "Virtuality"), or when we create a new table

2. Add CHECK section in the page where we edit an existing column, retrieving its current logic

3. Preview SQL should contain CHECK (at the moment, if we edit an existing column with CHECK, it will be lost!)

4. Display the CHECK logic in the Table Structure page

--

ALTER TABLE `table` ADD col TINYINT UNSIGNED NOT NULL DEFAULT 0 CHECK (col < 7)

ALTER ONLINE TABLE `table` CHANGE `col` `col` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 CHECK (col < 8)

[Wiktor102]

Any updates on this?

[mokraemer]

It is better to use table constraints:

ALTER TABLE XX ADD CONSTRAINT `basicCheck` CHECK (`remoteID` > 0)

But support in phymyadmin is still missing.

[williamdes]

Add a CHECK section in the page where we add a new column to a table (similar to "Virtuality"), or when we create a new table

Hi @nunoperalta
That looks like a good idea, is there only one check per column allowed?

[mokraemer]

if you do this per column, yes. And you can only the the column itself.
So it is better to specify it as a general constraint, this way you can detect errors, e.g. if column a is empty b must have a value

[nunoperalta]

if you do this per column, yes

You mean this, correct?

ALTER TABLE table ADD col TINYINT UNSIGNED NOT NULL DEFAULT 0 CHECK (col < 7)

--

So it is better to specify it as a general constraint

So it's better to do this, you mean:

ALTER TABLE table ADD CONSTRAINT basicCheck CHECK (remoteID > 0 AND someOtherColumn > 7)

--

Yeah, makes sense.

I didn't know that was possible.

Seems both MySQL and MariaDB support it:

https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

Post 8.0.16:

ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED];

https://mariadb.com/kb/en/constraint/

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

And https://mariadb.com/kb/en/alter-table/

[bradlanier]

PLEASE let's just add the feature! Is there any reason we can't have BOTH column and table level definitions for CHECK?

[Wiktor102]

I don't see any reason why we couldn't have both. It also seems like you can use column and table constraints together as can be seen in the examples provided in MariaDB documentation:
CREATE TABLE t1 (a INT CHECK (a>2), b INT CHECK (b>2), CONSTRAINT a_greater CHECK (a>b));

Is anybody willing to work on this?