ОГРАНИЧЕНИЯ

MariaDB поддерживает реализацию ограничений на уровне таблицы с помощью операторов CREATE TABLE или ALTER TABLE. Ограничение таблицы ограничивает данные, которые вы можете добавить в таблицу. Если вы попытаетесь вставить неверные данные в столбец, MariaDB выдаст ошибку.

Синтаксис

[CONSTRAINT [symbol]] constraint_expression

constraint_expression:
  | PRIMARY KEY [index_type] (index_col_name, ...) [index_option] ...
  | FOREIGN KEY [index_name] (index_col_name, ...) 
       REFERENCES tbl_name (index_col_name, ...)
       [ON DELETE reference_option]
       [ON UPDATE reference_option]
  | UNIQUE [INDEX|KEY] [index_name]
       [index_type] (index_col_name, ...) [index_option] ...
  | CHECK (check_constraints)

index_type:
  USING {BTREE | HASH | RTREE}

index_col_name:
  col_name [(length)] [ASC | DESC]

index_option:
  | KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | CLUSTERING={YES|NO}

reference_option:
  RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

Описание

Ограничения накладывают ограничения на данные, которые вы можете добавить в таблицу. Это позволяет вам обеспечивать целостность данных из MariaDB, а не через логику приложения. Когда оператор нарушает ограничение, MariaDB выдаёт ошибку.

Существует четыре типа ограничений таблицы:

Таблица Information Schema TABLE_CONSTRAINTS содержит информацию о таблицах, которые имеют ограничения.

Ограничения внешних ключей

InnoDB поддерживает ограничения внешних ключей. Синтаксис определения ограничения внешнего ключа в InnoDB выглядит так:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

Таблица Information Schema REFERENTIAL_CONSTRAINTS содержит дополнительную информацию об внешних ключах.

Ограничения CHECK

Ограничения применяются. Перед MariaDB 10.2.1 выражения ограничений принимались в синтаксисе, но игнорировались.

Вы можете определить ограничения двумя способами:

• CHECK(expression) указанные как часть определения столбца.
• CONSTRAINT [constraint_name] CHECK (expression)

Перед вставкой или обновлением строки все ограничения оцениваются в порядке их определения. Если любое выражение ограничения возвращает false, то строка не будет вставлена или обновлена. В ограничении можно использовать большинство детерминированных функций, включая UDF.

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

Если вы используете второй формат и не задаёте имя ограничению, то ограничение получит автоматически сгенерированное имя. Это делается для того, чтобы вы могли позже удалить ограничение с помощью ALTER TABLE DROP constraint_name.

Можно отключить все проверки выражений ограничений, установив переменную check_constraint_checks в значение OFF. Это полезно, например, при загрузке таблицы, которая нарушает некоторые ограничения, которые вы хотите найти и исправить позже в SQL.

Репликация

В базовой репликации только мастер проверяет ограничения, а невыполненные операторы не будут реплицированы. В операторной репликации клиенты также будут проверять ограничения. Поэтому ограничения должны быть идентичными и детерминированными в среде репликации.

Автоинкремент

Столбцы auto_increment не допускаются в ограничениях CHECK. Перед MariaDB 10.2.6 они допускались, но не работали правильно. См. MDEV-11117.

Примеры

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                       PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                            product_category INT NOT NULL,
                            product_id INT NOT NULL,
                            customer_id INT NOT NULL,
                            PRIMARY KEY(no),
                            INDEX (product_category, product_id),
                            FOREIGN KEY (product_category, product_id)
                              REFERENCES product(category, id)
                              ON UPDATE CASCADE ON DELETE RESTRICT,
                            INDEX (customer_id),
                            FOREIGN KEY (customer_id)
                              REFERENCES customer(id)) ENGINE=INNODB;

Следующие примеры будут работать начиная с MariaDB 10.2.1.

Числовые ограничения и сравнения:

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

INSERT INTO t1(a) VALUES (1);
ERROR 4022 (23000): CONSTRAINT `a` failed for `test`.`t1`

INSERT INTO t1(a,b) VALUES (3,4);
ERROR 4022 (23000): CONSTRAINT `a_greater` failed for `test`.`t1`

INSERT INTO t1(a,b) VALUES (4,3);
Query OK, 1 row affected (0.04 sec)

Удаление ограничения:

ALTER TABLE t1 DROP CONSTRAINT a_greater;

Добавление ограничения:

ALTER TABLE t1 ADD CONSTRAINT a_greater CHECK (a>b);

Сравнения дат и длина символов:

CREATE TABLE t2 (name VARCHAR(30) CHECK (CHAR_LENGTH(name)>2), start_date DATE, 
  end_date DATE CHECK (start_date IS NULL OR end_date IS NULL OR start_date<end_date));

INSERT INTO t2(name, start_date, end_date) VALUES('Ione', '2003-12-15', '2014-11-09');
Query OK, 1 row affected (0.04 sec)

INSERT INTO t2(name, start_date, end_date) VALUES('Io', '2003-12-15', '2014-11-09');
ERROR 4022 (23000): CONSTRAINT `name` failed for `test`.`t2`

INSERT INTO t2(name, start_date, end_date) VALUES('Ione', NULL, '2014-11-09');
Query OK, 1 row affected (0.04 sec)

INSERT INTO t2(name, start_date, end_date) VALUES('Ione', '2015-12-15', '2014-11-09');
ERROR 4022 (23000): CONSTRAINT `end_date` failed for `test`.`t2`

Неправильная скобка:

CREATE TABLE t3 (name VARCHAR(30) CHECK (CHAR_LENGTH(name>2)), start_date DATE, 
  end_date DATE CHECK (start_date IS NULL OR end_date IS NULL OR start_date<end_date));
Query OK, 0 rows affected (0.32 sec)

INSERT INTO t3(name, start_date, end_date) VALUES('Io', '2003-12-15', '2014-11-09');
Query OK, 1 row affected, 1 warning (0.04 sec)

SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Io' |
+---------+------+----------------------------------------+

Сравните определение таблицы t2 с таблицей t3. CHAR_LENGTH(name)>2 существенно отличается от CHAR_LENGTH(name>2), так как последнее ошибочно выполняет числовое сравнение поля name, что приводит к неожиданным результатам.

См. также

• Внешние ключи