Как добавить пользовательское ограничение CHECK для таблицы MySQL?

Ответ 1

CHECK ограничения не поддерживаются MySQL. Вы можете определить их, но они ничего не делают (с MySQL 5.7).

Из руководства :

Предложение CHECK анализируется, но игнорируется всеми механизмами хранения.

Обходным путем является создание triggers, но это не самая простая задача для работы.

Если вам нужна RDBMS с открытым исходным кодом, которая поддерживает ограничения CHECK, попробуйте PostgreSQL. Это действительно очень хорошая база данных.

Ответ 2

Я не понимаю, почему никто здесь не упомянул, что VIEW WITH CHECK OPTION может быть хорошей альтернативой CHECK CONSTRAINT в MySQL:

CREATE VIEW name_of_view AS SELECT * FROM your_table
WHERE <condition> WITH [LOCAL | CASCADED] CHECK OPTION;

На сайте MySQL есть документ: Предложение View With CHECK OPTION

DROP TABLE 'Participants';

CREATE TABLE 'Participants' (
  'meetid' int(11) NOT NULL,
  'pid' varchar(15) NOT NULL,
  'status' char(1) DEFAULT NULL check (status IN ('a','d','u')),
  PRIMARY KEY ('meetid','pid')
);

-- should work
INSERT INTO 'Participants' VALUES (1,1,'a');
-- should fail but doesn't because table check is not implemented in MySQL
INSERT INTO 'Participants' VALUES (2,1,'x');

DROP VIEW vParticipants;
CREATE VIEW vParticipants AS 
  SELECT * FROM Participants WHERE status IN ('a','d','u')
  WITH CHECK OPTION;

-- should work
INSERT INTO vParticipants VALUES (3,1,'a');
-- will fail because view uses a WITH CHECK OPTION
INSERT INTO vParticipants VALUES (4,1,'x');

PS: имейте ввиду, что ваше мнение должно быть обновляемым! Смотрите MySQL Updatable Views (спасибо Ромео Сьерра за разъяснения в комментариях).

Ответ 3

Помимо триггеров, для простых ограничений, подобных тем, которые у вас есть:

CONSTRAINT `participants_ibfk_2` 
  CHECK status IN ('a','d','u')

вы можете использовать Foreign Key из status в справочную таблицу (ParticipantStatus с тремя строками: 'a','d','u'):

CONSTRAINT ParticipantStatus_Participant_fk
  FOREIGN KEY (status)
    REFERENCES ParticipantStatus(status) 

Ответ 4

Вот способ быстрого и простого получения проверок:

drop database if exists gtest;

create database if not exists gtest;
use gtest;

create table users (
  user_id       integer unsigned not null auto_increment primary key,
  username      varchar(32) not null default '',
  password      varchar(64) not null default '',
  unique key ix_username (username)
) Engine=InnoDB auto_increment 10001;

create table owners (
  owner_id      integer unsigned not null auto_increment primary key,
  ownername     varchar(32) not null default '',
  unique key ix_ownername (ownername)
) Engine=InnoDB auto_increment 5001;

create table users_and_owners (
  id    integer unsigned not null primary key,
  name  varchar(32) not null default '',
  unique key ix_name(name)
) Engine=InnoDB;

create table p_status (
  a_status      char(1) not null primary key
) Engine=InnoDB;

create table people (
  person_id integer unsigned not null auto_increment primary key,
  pid       integer unsigned not null,
  name      varchar(32) not null default '',
  status    char(1) not null,
  unique key ix_name (name),
  foreign key people_ibfk_001 (pid) references users_and_owners(id),
  foreign key people_ibfk_002 (status) references p_status (a_status)
) Engine=InnoDB;

create or replace view vw_users_and_owners as
select 
  user_id id,
  username name
from users
union
select 
  owner_id id,
  ownername name
from owners
order by id asc
;

create trigger newUser after insert on users for each row replace into users_and_owners select * from vw_users_and_owners;
create trigger newOwner after insert on owners for each row replace into users_and_owners select * from vw_users_and_owners;

insert into users ( username, password ) values
( 'fred Smith', password('fredSmith')),
( 'jack Sparrow', password('jackSparrow')),
( 'Jim Beam', password('JimBeam')),
( 'Ted Turner', password('TedTurner'))
;

insert into owners ( ownername ) values ( 'Tom Jones'),( 'Elvis Presley'),('Wally Lewis'),('Ted Turner');

insert into people (pid, name, status) values ( 5001, 'Tom Jones', 1),(10002,'jack Sparrow',1),(5002,'Elvis Presley',1);

Ответ 5

Как я объяснил в этой статье, начиная с версии 8.0.16, MySQL добавил поддержку ограничений CHECK:

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
);

Ранее это было доступно только с использованием триггеров BEFORE INSERT и BEFORE UPDATE:

CREATE
TRIGGER post_content_check BEFORE INSERT
ON topic
FOR EACH ROW
BEGIN
   IF NEW.DTYPE = 'Post'
   THEN
       IF NEW.content IS NULL
       THEN
           signal sqlstate '45000'
           set message_text = 'Post content cannot be NULL';
       END IF;
   END IF;
END;

CREATE
TRIGGER post_content_update_check BEFORE UPDATE
ON topic
FOR EACH ROW
BEGIN
   IF NEW.DTYPE = 'Post'
   THEN
       IF NEW.content IS NULL
       THEN
           signal sqlstate '45000'
           set message_text = 'Post content cannot be NULL';
       END IF;
   END IF;
END;

CREATE
TRIGGER announcement_validUntil_check BEFORE INSERT
ON topic
FOR EACH ROW
BEGIN
   IF NEW.DTYPE = 'Announcement'
   THEN
       IF NEW.validUntil IS NULL
       THEN
           signal sqlstate '45000'
           set message_text = 'Announcement validUntil cannot be NULL';
       END IF;
   END IF;
END;

CREATE
TRIGGER announcement_validUntil_update_check BEFORE UPDATE
ON topic
FOR EACH ROW
BEGIN
   IF NEW.DTYPE = 'Announcement'
   THEN
       IF NEW.validUntil IS NULL
       THEN
           signal sqlstate '45000'
           set message_text = 'Announcement validUntil cannot be NULL';
       END IF;
   END IF;
END;

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