There are two tables I'm dealing with, one for time slots and another for reservations. In the slots table is a column "spots", default value 0. A maximum of 3 people can sign up for one time slot, which I'm partially handling with this code:
$query = "UPDATE slotTest SET spots=spots+1 WHERE slotID=$slotID;";
...meaning that, every time someone signs up for a slot, "spots" gets augmented by one (assuming it hasn't already reached max value 3).
So my question is - can I use mysql foreign key constraints to automatically reduce "spots" by 1 when a person cancels a reservation, i.e. deletes their entry from the reservations table? It currently has three columns (resID, name, slotID) where resID is the primary key and slotID is a foreign key linked to primary key slotID from the slots table. Do I also need to turn "spots" in the slots table into a foreign key linked to resID? If so, what options should I set in phpmyadmin's "Relation View" to get the effect I'm after?
