Add support for CHECK constraints (feature request)

[stevenwdv]

MariaDB (MySQL-compatible) added support for CHECK constraints to version 10.2.1 (stable=10.2.8). It would be very useful if PHPMyAdmin had a UI for adding and managing these in tbl_create.php and tbl_structure.php.
(Update: see also phpmyadmin/sql-parser#167)

Edit: they are also not exported by export.php.

[aroralakshya]

The MySQL Reference Manual says:
The CHECK clause is parsed but ignored by all storage engines.

Triggers should be used instead of check constraint.

[Gemorroj]

@nulll-pointer MariaDB normally supports check constraint.
see:
https://mariadb.com/kb/en/library/check-constraints-support/
https://mariadb.com/kb/en/library/constraint/#check-constraints

[aroralakshya]

It's ignored in my version of mariaDB

Have you tried implementing?

[Gemorroj]

@nulll-pointer you use mariadb 10.1.30, but support check constraints added in 10.2.1 version.

[ibennetch]

@nulll-pointer according to the documentation link posted by Gemorroj, your MariaDB is too old. MySQL ignoring it doesn't mean we shouldn't implement it for MariaDB.

[ibennetch]

I see this as a three-part issue:

• Update parser to understand this syntax
• Update the export interface to allow including these in an export
• Implement an interface for interacting with these constraints

[maherbo]

Using version 4.7.4, this is more than enhancement, it is a bug.

Right now, you can add the CHECK constraint with a custom SQL statement. Even though the parser indicates a syntax error, it works.

But, when you try to export the table, the CHECK constraint is unrecognized and is ignored in the CREATE TABLE statement ... but so are ALL columns definitions afterward. That is a bug.

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