检查MySQL的约束 [英] Check constraint for mysql

问题描述

谁能帮助我为MYSQL中的检查约束"正确地编写正确的语法.我的表如下，并且在声明STATUS的CHECK约束时出现错误.

Can anyone help me correctly write the correct syntax for a CHECK CONSTRAINT IN MYSQL. My table is as follows and am having an error on declaring a CHECK constraint for STATUS.

CREATE TABLE EventRequest (
EventNo CHAR(8) NOT NULL, 
DateHeld DATE NOT NULL, 
DateReq DATE NOT NULL,
FacNo CHAR(8)NOT NULL,
CustNo CHAR(8) NOT NULL,
DateAuth DATE,
Status CHAR(8)NOT NULL, 
EstCost DECIMAL(9,4)NOT NULL,
EstAudience INT(6) NOT NULL,
BudNo VARCHAR2(8), 
CONSTRAINT PK_EVENTREQUEST PRIMARY KEY (EventNo),
CONSTRAINT FK_FACNO FOREIGN KEY (FacNo) REFERENCES FACILITY (FacNo),
CONSTRAINT FK_CUSTNO FOREIGN KEY (CustNo) REFERENCES CUSTOMER (CustNo),
CONSTRAINT Check_Status
CHECK (Status IN ('Approved','Pending','Denied') ) ) ;

推荐答案

MySQL不支持CHECK约束.

MySQL does not support CHECK constraints.

多年来，它一直是一个愿望清单项目( https://bugs.mysql. com/bug.php?id = 3464 ).

It has been a wishlist item for years (https://bugs.mysql.com/bug.php?id=3464).

MySQL团队已发布有关解决方法的博客:

The MySQL team has posted a blog about workarounds: http://mysqlserverteam.com/new-and-old-ways-to-emulate-check-constraints-domain/

在您的情况下，您可以考虑:

In your case you could consider:

CONSTRAINT Check_Status FOREIGN KEY (Status) REFERENCES StatusTypes (Status)

然后创建一个表StatusTypes，其中包含要限制它的三行.

And then create a table StatusTypes with the three rows you want to restrict it to.

这篇关于检查MySQL的约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！