-1

I am trying to put a constraint on a column when creating a table, namely I want 'numberOfNights' to be restricted to >=1 and <=3. I cannot get it to work; I think I may need a trigger but I am unsure. Part of the reason I am confused is because I used the CHECK constraint when creating a previous table in the same database (using the same syntax also) and that was successful, yet for my new table it is not. This is what I'm having trouble with:

CREATE TABLE city_CC (
   cityName VARCHAR(255) PRIMARY KEY,
   tourID INT, FOREIGN KEY REFERENCES tour_CC(tourID),
   hotelName VARCHAR(25) NOT NULL,
   numberOfNights INT CHECK(numberOfNights >=1 AND <=3)
);

My previous example from another table which seems to have worked is:

CREATE TABLE tour_CC (
   tourID INT NOT NULL AUTO_INCREMENT,
   tourTitle VARCHAR(15) NOT NULL,
   cost DECIMAL(6,2) DEFAULT 2000.00
   CHECK (cost>=1500.00 AND cost<=2500.00)
);

Why does the CHECK constraint work in the tour_CC table and not with the same syntax in the city_CC table? If I require a trigger with the city table, then why is one not required for the tour table?

Thanks for reading and for any help in advance.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Thelonghaul91
  • 11

1 Answers1

1

MySQL accepts the syntax of check constraints, but does not actually do the check.

If you really want to check the values, you need to use a trigger.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • +10. CHECK constraints are *not* enforced by MySQL.(And it's not a bug, because the behavior is documented somewhere in the MySQL Reference Manual.) – spencer7593 Apr 05 '18 at 23:38
  • I see, that makes a bit more sense then. So I would need triggers for both tables then - I'm guessing it would be BEFORE INSERT on both of the tables? thanks for your reply! – Thelonghaul91 Apr 05 '18 at 23:40
  • @Thelonghaul91 . . . To validate values, you want a BEFORE INSERT trigger. – Gordon Linoff Apr 06 '18 at 01:48