[SOLVED] Adding a named check constraint to a table

Adding a named check constraint to a table

I am trying to add a named CHECK constraint to a table in MariaDB. As far as I can tell from the documentation and a few samples scattered around, the following syntax shouyld work:

DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
    id int AUTO_INCREMENT PRIMARY KEY,
    name varchar(255),
    email VARCHAR(60) constraint customer_email check (email like '%@%')
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci ENGINE=INNODB;

It doesn’t. I get the ever-so-helpful message:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'check (email like '%@%') ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_...' at line 4

which, of course tells me nothing.

In know that

if I remove constraint customer_email it works
if I add a comma after the email VARCHAR(60), making the check constraint a separate command it also works

Does that mean the MariaDB doesn’t support the above syntax, or is there something I’ve missed?

I have a fiddle at https://dbfiddle.uk/PYJKnmuU .

Solution

According to https://mariadb.com/kb/en/constraint/#check-constraints, it appears that you can use named constraints only if you define the constraint as a table-level constraint.

In other words, this should work:

...
email VARCHAR(60),    -- notice the comma
constraint customer_email check (email like '%@%')
...

But not defining the named check constraint inline with the respective column as you were doing.

The comma is the important bit in this case. Adding a newline after the comma is optional, because SQL generally treats whitespace as optional in almost all cases.

The syntax allows an inline CHECK constraint with the column, but not if you need to name the constraint.