如何在 MySQL 表上添加自定义 CHECK 约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7522026/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I add a custom CHECK constraint on a MySQL table?
提问by Mathias Bak
I am having trouble with this table
这张桌子有问题
CREATE TABLE `Participants` (
`meetid` int(11) NOT NULL,
`pid` varchar(15) NOT NULL,
`status` char(1) DEFAULT NULL,
PRIMARY KEY (`meetid`,`pid`),
CONSTRAINT `participants_ibfk_1` FOREIGN KEY (`meetid`) REFERENCES `Meetings` (`meetid`) ON DELETE CASCADE
CONSTRAINT `participants_ibfk_2` CHECK (status IN ('a','d','u'))
CONSTRAINT `participants_ibfk_3` CHECK (pid IN (SELECT name FROM Rooms) OR pid IN (SELECT userid FROM People))
);
I want to have a foreign key constraint, and that works. Then, I also want to add a constraint to the attribute statusso it can only take the values 'a', 'd' and 'u'. It is not possible for me to set the field as Enumor set.
我想要一个外键约束，这很有效。然后，我还想为属性添加一个约束，status以便它只能采用值“a”、“d”和“u”。我无法将字段设置为Enumor set。
Can anyone tell me why this code does not work in MySQL?
谁能告诉我为什么这段代码在 MySQL 中不起作用？
采纳答案by Vlad Mihalcea
As I explained in this article, starting with version 8.0.16, MySQL has added support for CHECK constraints:
正如我在本文中解释的那样，从 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
);
Previously, this was only available using BEFORE INSERT and BEFORE UPDATE triggers:
以前，这只能使用 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;
For more details about emulating CHECK constraints using database triggers for MySQL versions prior to 8.0.16, then check out this article.
有关使用 MySQL 8.0.16 之前版本的数据库触发器模拟 CHECK 约束的更多详细信息，请查看这篇文章。
回答by NullUserException
CHECKconstraints are not supported by MySQL. You can define them, but they do nothing (as of MySQL 5.7).
CHECKMySQL 不支持约束。您可以定义它们，但它们什么都不做（从 MySQL 5.7 开始）。
From the manual:
从手册：
The
CHECKclause is parsed but ignored by all storage engines.
该
CHECK子句被解析但被所有存储引擎忽略。
The workaround is to create triggers, but they aren't the easiest thing to work with.
解决方法是创建triggers，但它们不是最容易使用的东西。
If you want an open-source RDBMS that supports CHECKconstraints, try PostgreSQL. It's actually a very good database.
如果您想要一个支持CHECK约束的开源 RDBMS ，请尝试PostgreSQL。它实际上是一个非常好的数据库。
回答by guzoff
I don't understand why nobody here has mentioned that VIEW WITH CHECK OPTIONcan be a good alternative to the CHECK CONSTRAINTin MySQL:
我不明白为什么这里没有人提到VIEW WITH CHECK OPTION可以替代MySQL 中的CHECK CONSTRAINT：
CREATE VIEW name_of_view AS SELECT * FROM your_table
WHERE <condition> WITH [LOCAL | CASCADED] CHECK OPTION;
There is a doc on the MySQL site: The View WITH CHECK OPTION Clause
MySQL 站点上有一个文档：The View WITH CHECK OPTION Clause
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');
P.S.:Keep in mind that your view should be updatable! See MySQL Updatable Views(thanks Romeo Sierra for clarification in comments).
PS：请记住，您的视图应该是可更新的！请参阅MySQL 可更新视图（感谢 Romeo Sierra 在评论中进行澄清）。
回答by ypercube??
Beside triggers, for simple constraints like the one you have:
除了触发器，对于简单的约束，比如你拥有的约束：
CONSTRAINT `participants_ibfk_2`
CHECK status IN ('a','d','u')
you could use a Foreign Keyfrom statusto a Reference table (ParticipantStatuswith 3 rows: 'a','d','u'):
您可以使用Foreign Keyfromstatus到 Reference 表（ParticipantStatus有 3 行：）'a','d','u'：
CONSTRAINT ParticipantStatus_Participant_fk
FOREIGN KEY (status)
REFERENCES ParticipantStatus(status)
回答by Biztux
Here is a way of getting the checks you wanted quickly and easily:
这是一种快速轻松地获得您想要的支票的方法：
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')),
( 'Hyman Sparrow', password('HymanSparrow')),
( '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,'Hyman Sparrow',1),(5002,'Elvis Presley',1);
