Check constraint for mysql

Question

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

I think `CHECK CONSTRAINT`, `VARCHAR2 ` is supported in `Oracle` only, not in `Mysql`. — Mayank Pandeyz, Sep 29 '17 at 06:38

score 3 · Answer 1 · answered Sep 29 '17 at 06:39

3

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.

answered Sep 29 '17 at 06:39

Bill Karwin

538,548
86
673
828

It's worth noting that while MySQL doesn't have check constraints, [MariaDB does have them](https://mariadb.com/kb/en/library/constraint/#check-constraints) since version 10.2. – dbdemon Mar 06 '18 at 21:21
MySQL supports CHECK constraint since [8.0.16](https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html). – Soul Reaver May 19 '20 at 08:58
Correct. See https://stackoverflow.com/a/2115641/20860 – Bill Karwin May 19 '20 at 19:38

score 1 · Answer 2 · answered Oct 01 '17 at 07:24

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

  CREATE TABLE EventRequest  (
  EventNo VARCHAR(8) NOT NULL COMMENT 'Event number', 
  DateHeld DATE NOT NULL COMMENT 'Event date', 
  DateReq DATE NOT NULL COMMENT 'Date requested', 
  CustNo VARCHAR(8) NOT NULL COMMENT 'Customer number', 
  FacNo VARCHAR(8) NOT NULL COMMENT 'Facility number', 
  DateAuth DATE COMMENT 'Date authorized', 
  Status VARCHAR(20) NOT NULL COMMENT 'Status of event request' CHECK 
  (Status IN ('Pending', 'Denied', 'Approved')), 
  EstCost DECIMAL(15,4) NOT NULL COMMENT 'Estimated cost', 
  EstAudience DECIMAL(11,0) NOT NULL 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) );

Check constraint for mysql

2 Answers2

Linked