Creating a DOMAIN in MySQL or MariaDB?

Question

As far as I know, DOMAINS cannot be created in MySQL. Is there any disadvantage for that? Is there something to be used instead of using CREATE DOMAIN?

Answer 1

No, check constraints are supported by MariaDB. Domains are user defined datatypes. The domain mechanism allows you to put constraints on the datatype instead of having to put constraints on every single column where the specific datatype is being used.

Answer 2

Both MySQL and MariaDB currently lack this feature. DOMAINs are a method of associating a constraint with a type creating a pseudo-user defined type. Those constraints that will otherwise be attached to the DOMAIN will have to be placed on everything that uses that type, and managed there.

I opened a feature request SQL Standard DOMAIN functionality, MDEV-16377

Answer 3

_{Community wiki answer:}

By domain, do you mean that MySQL does not support check constraints? If so, then triggers are an option, as mentioned in the Wikipedia entry for Data domain.

MySQL is missing many of the features that are now in other RDBMSs - CTEs (WITH clause), Window/Analytic functions, CHECK constraints, SET operators. In fact, MySQL is missing a lot of syntactic sugar. It is, however, gradually fattening itself up; just not with your favorite sweets.

If you're at the beginning of a project, you might want to consider a different database product. You can do a lot with triggers, but it's a lot more work/design/debugging etc.

Answer 4

You can use check constraints, available as of MySQL 8.0.16 and MariaDB 10.2.1.

CREATE TABLE t (a INT CHECK (a>5));

or

CREATE TABLE t (a INT, CONSTRAINT greater_than_5 CHECK ((a>5)));

Refer to https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html and https://mariadb.com/kb/en/library/constraint/.