如何向表添加检查限制？ [英] How do I add a check constratint to a table?

问题描述

我在使用这个表格时遇到问题

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

我想有一个外键约束，然后我想添加一个约束到属性 status ，所以它只能取值'a'，'d'和'u'。我不能将字段设置为 Enum 或设置。

I want to have a foreign key constraint, and that works. Then i want to add a constraint to the attribute status so it can only take the values 'a', 'd' and 'u'. It is not possible for me to set the field as Enum or set.

任何人都可以告诉我为什么这个代码在MySQL中不起作用。

Can anyone tell me why this code does not work in MySQL?

推荐答案

CHECK 约束不受MySQL支持。你可以定义它们，但它们什么也不做（从MySQL 5.7开始）。

CHECK constraints are not supported by MySQL. You can define them, but they do nothing (as of MySQL 5.7).

从手动 a>：

CHECK 子句被解析，但被所有存储引擎忽略。

The CHECK clause is parsed but ignored by all storage engines.

解决方法是创建 triggers ，但它们并不是最简单的处理方式。

The workaround is to create triggers, but they aren't the easiest thing to work with.

如果您希望开放源代码RDBMS支持 CHECK 约束，请尝试 PostgreSQL 。它实际上是一个非常好的数据库。

If you want an open-source RDBMS that supports CHECK constraints, try PostgreSQL. It's actually a very good database.

这篇关于如何向表添加检查限制？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！