0

I am designing a database for a tour event. I need to write a check constraint that monitors the tour ticket sales (TicketID) and tourcapacity. it stops sell of tickets when the maximum tourcapacity is reached. how do i write the code for a tourcapacity of 20 people? I am new to MySQL so forgive my limited knowledge. Thanks

CREATE TABLE ticket(
TicketID integer not null, 
TicketPrice DOUBLE(10,2) NOT NULL DEFAULT 0.0, 
PurchaseDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
CustomerID integer NOT NULL,
PRIMARY KEY (TicketID)
);

CREATE TABLE tour(
TourID integer not null, 
StartDate date not null,  
StartTime time not null, 
EndTime time not null, 
TicketID integer not null, 
StaffID integer not null, 
LocationID integer not null, 
Tourcapacity interger not null,
PRIMARY KEY (TourID)
);
fancyPants
  • 50,732
  • 33
  • 89
  • 96
Joseph
  • 313
  • 1
  • 4
  • 16
  • 2
    MySQL doesn't support check constraints yet. You can specify them, but they'll be ignored. – fancyPants Nov 10 '14 at 07:58
  • Hi, thanks for your comment. you are right mysql doesn't enforce check constraints. I read somewhere that you can use foreign key to achieve the desired result but the explanation given was to complicated for me. Can you give me a work around that achieves the same result using foreign key. Thanks – Joseph Nov 10 '14 at 08:19
  • Foreign keys aren't good at counting (nor are most solutions). Given the multi-table nature and the counting required, I think you'll be looking for a trigger based solution rather than any declarative approach. Also, if `TourCapacity` is updatable, don't forget to include a trigger for that event also. (e.g. TourCapacity is 20, 15 tickets have been created, someone attempts to reduce TourCapacity down to 10) – Damien_The_Unbeliever Nov 10 '14 at 08:25
  • @Damien_The_Unbeliever, your soultion sounds good. but I am at sea on how to go about it. Please can you give a code example. Thanks – Joseph Nov 10 '14 at 08:50

0 Answers0