Check Constraint For Mysql

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. CRE

Solution 1:

MySQL does not support CHECK constraints.

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

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)

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

Solution 2:

Thank you all to those who tried to help, finally managed to make it work with the code below

CREATETABLE EventRequest  (
  EventNo VARCHAR(8) NOTNULL COMMENT 'Event number', 
  DateHeld DATENOTNULL COMMENT 'Event date', 
  DateReq DATENOTNULL COMMENT 'Date requested', 
  CustNo VARCHAR(8) NOTNULL COMMENT 'Customer number', 
  FacNo VARCHAR(8) NOTNULL COMMENT 'Facility number', 
  DateAuth DATE COMMENT 'Date authorized', 
  Status VARCHAR(20) NOTNULL COMMENT 'Status of event request'CHECK 
  (Status IN ('Pending', 'Denied', 'Approved')), 
  EstCost DECIMAL(15,4) NOTNULL COMMENT 'Estimated cost', 
  EstAudience DECIMAL(11,0) NOTNULL COMMENT 'Estimated audience'CHECK 
  (EstAudience >0), 
  BudNo VARCHAR(8) COMMENT 'Budget number',
  CONSTRAINT PK_EVENTREQUEST PRIMARY KEY (EventNo),
  CONSTRAINT FK_EVENT_FACNO FOREIGN KEY (FacNo) REFERENCES FACILITY (FacNo),
  CONSTRAINT FK_CUSTNO FOREIGN KEY (CustNo) REFERENCES CUSTOMER (CustNo) );