MySQL 8 поддерживает пользовательские ограничения проверки SQL

Вступление

В этой статье мы собираемся протестировать реализацию пользовательских ограничений проверки SQL в MySQL 8. Хотя предложение CHECK является стандартной функцией SQL, до MySQL 8.0.16 это предложение анализировалось и игнорировалось, поэтому эту функциональность можно было эмулировать только с помощью триггеров ВСТАВКИ и ОБНОВЛЕНИЯ.

Как протестировать реализацию пользовательских ограничений проверки SQL в MySQL 8 @vlad_mihalcea https://t.co/4rDn4J6K5p pic.twitter.com/kPWL6aFIZ2

Пользовательские ограничения ПРОВЕРКИ SQL

Как я объяснил в этой статье , пользовательские ограничения ПРОВЕРКИ SQL очень полезны для обеспечения ограничений, не допускающих обнуления, для атрибутов подкласса сущности JPA при использовании стратегии наследования JPA для ОДНОЙ ТАБЛИЦЫ.

Чтобы понять проблему, рассмотрим, что у нас есть следующая модель предметной области:

При использовании стратегии наследования ОДНОЙ ТАБЛИЦЫ базовый класс и все подклассы используют одну и ту же таблицу базы данных:

Однако, если атрибуты, относящиеся к подклассу, такие как content и validUntil атрибуты Post и Объявление сущностей, должны быть ненулевыми, мы не можем просто добавить ограничение NOT NULL на уровне столбца SQL, так как в противном случае мы не сможем добавить запись post как valid_until столбец NOT NULL ограничение не будет выполнено.

Именно в этом нам могут помочь ограничения пользовательских проверок SQL. Поэтому мы могли бы добавить следующие два КОНТРОЛЬНЫХ ограничения:

ALTER TABLE topic 
ADD CONSTRAINT post_content_check 
CHECK (
    CASE 
        WHEN DTYPE = 'Post'
        THEN 
            CASE 
                WHEN content IS NOT NULL 
                THEN 1 
                ELSE 0 
            END
        ELSE 1
    END = 1
)

ALTER TABLE topic 
ADD CONSTRAINT announcement_validUntil_check 
CHECK (
    CASE 
        WHEN DTYPE = 'Announcement'
        THEN 
            CASE 
                WHEN validUntil IS NOT NULL 
                THEN 1 
                ELSE 0 
            END
        ELSE 1
    END = 1
)

MySQL 8 поддерживает пользовательские ограничения проверки SQL

Теперь, если мы попытаемся сохранить Сообщение сущность без действительного содержимого свойства:

entityManager.persist(new Post());

MySQL 8.0.16 собирается выдать следующее Исключение ConstraintViolationException :

INSERT INTO topic (
    board_id, 
    createdOn, 
    owner, 
    title, 
    content, 
    DTYPE, 
    id
)
VALUES (
    NULL(BIGINT), 
    '2019-07-03 10:40:03.933', 
    NULL(VARCHAR), 
    NULL(VARCHAR), 
    NULL(VARCHAR), 
    'Post', 
    4
)

-- SQL Error: 1644, SQLState: 45000
-- Post content cannot be NULL

То же самое произойдет, если мы попытаемся обновить существующую сущность Post и установим атрибут content равным null :

Post post = entityManager
.createQuery(
    "select p " +
    "from Post p " +
    "where p.content = :content", Post.class)
.setParameter("content", "Best practices")
.getSingleResult();

post.setContent(null);

MySQL 8.0.16 собирается создать исключение ConstraintViolationException , потому что пользовательское ограничение post_content_check CHECK не проверяет инструкцию UPDATE:

UPDATE topic 
SET 
    board_id = 1, 
    createdOn = '2019-07-03 10:45:53.581', 
    owner = 'John Doe', 
    title = 'Inheritance', 
    content = NULL(VARCHAR) 
WHERE 
    id = 2

-- SQL Error: 1644, SQLState: 45000
-- Post content cannot be NULL

Потрясающе, правда?

Если вы хотите узнать, что вам нужно было сделать, чтобы эмулировать ограничение CHECK для MySQL до 8.0.16. затем ознакомьтесь с этой статьей .

Вывод

Если вы все еще используете более старую версию MySQL, вам определенно следует рассмотреть возможность обновления до версии 8.0 , поскольку в MySQL было добавлено много функций в этом основном приращении версии (например , функции окон, ПРОПУСК ЗАБЛОКИРОВАН и NOWAIT, CTE или Рекурсивный CTE ).