indexes and constraints not created in CREATE TABLE statement

Question

I am using MySQL/MariaDB, and I create an employees table:

CREATE TABLE employees(
    id INT AUTO_INCREMENT,
    name VARCHAR(40) NOT NULL,
    description VARCHAR(50) DEFAULT 'No Description',
    random_assignment_id INT UNIQUE, 
    birth_date DATE, 
    salary DECIMAL(5,2),
    supervisor_id INT,
    branch_id INT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT random_assignment_check CHECK (LENGTH(random_assignment_id) = 5),
    INDEX(random_assignment_id, supervisor_id, branch_id),
    PRIMARY KEY(id)
)

Then I confirm the table is created as expected:

SHOW CREATE TABLE employees\G;
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `description` varchar(50) DEFAULT 'No Description',
  `random_assignment_id` int(11) DEFAULT NULL,
  `birth_date` date DEFAULT NULL,
  `salary` decimal(5,2) DEFAULT NULL,
  `supervisor_id` int(11) DEFAULT NULL,
  `branch_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `random_assignment_id` (`random_assignment_id`),
  KEY `random_assignment_id_2` (`random_assignment_id`,`supervisor_id`,`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

I don't see the random_assignment_check constraint listed, and I expected it to index random_assignment_id, supervisor_id and branch_id, but it does not:

 DESCRIBE employees;
+----------------------+--------------+------+-----+-------------------+----------------+
| Field                | Type         | Null | Key | Default           | Extra          |
+----------------------+--------------+------+-----+-------------------+----------------+
| id                   | int(11)      | NO   | PRI | NULL              | auto_increment |
| name                 | varchar(40)  | NO   |     | NULL              |                |
| description          | varchar(50)  | YES  |     | No Description    |                |
| random_assignment_id | int(11)      | YES  | UNI | NULL              |                |
| birth_date           | date         | YES  |     | NULL              |                |
| salary               | decimal(5,2) | YES  |     | NULL              |                |
| supervisor_id        | int(11)      | YES  |     | NULL              |                |
| branch_id            | int(11)      | NO   |     | NULL              |                |
| created_at           | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| updated_at           | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+----------------------+--------------+------+-----+-------------------+----------------+

There are no MUL flags under key.

Note that I read MariaDB now supports Constraints; according to homebrew, I am using:

brew info mariadb
mariadb: stable 10.3.12 (bottled)
Drop-in replacement for MySQL

What am I doing wrong?

Is replication involved? (CHECKs are not performed on Slaves.) — Rick James, Jan 24 '19 at 05:44
Yet another reason why `DESCRIBE` is less descriptive than `SHOW CREATE TABLE`. — Rick James, Jan 24 '19 at 05:49

Bill Karwin · Accepted Answer · 2019-01-22T16:28:18.413

MySQL treats KEY as a synonym for INDEX. Your index INDEX(random_assignment_id, supervisor_id, branch_id) became KEY random_assignment_id_2 (random_assignment_id,supervisor_id,branch_id). The index name was generated by MySQL, but logically they're the same index.

When I tested your CREATE TABLE statement and then use DESC to display it, I also see no MUL indicators. This fits the documentation:

If Key is MUL, the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column.

So in your output, the Key field for random_assignment_id is UNI because it's a unique key, in addition to part of a multi-column key.

MySQL doesn't support CHECK constraints. It parses them, then ignores them. They are not stored with your table, and subsequently using SHOW CREATE TABLE doesn't show them.

MariaDB has implemented CHECK constraints in 10.2.1 according to https://mariadb.com/kb/en/library/constraint/#check-constraints (I don't use MariaDB, so I'll trust their doc).

It's not clear from your question if you tested your CHECK constraint on MySQL Community Edition or MariaDB. CHECK constraint will not be saved on MySQL, and on MariaDB it seems okay per the doc, but I have never tested it.

We should all stop thinking of MariaDB a drop-in replacement for MySQL. The two products have been diverging for nearly 10 years, and they can no longer be assumed to be compatible.

Exactly, you could also mention `SHOW INDEXES FROM ;` though. Anyway, good answer as always. — fancyPants, Jan 22 '19 at 16:05
Ohhh, I see what I did wrong. I created a multi-column key, when in fact, I did not mean to. Each of the three columns were intended to be separate keys. — Daniel Viglione, Jan 24 '19 at 16:24

indexes and constraints not created in CREATE TABLE statement

1 Answers1