Change the Timeslot table Private Key
In this Merge Request, the private key of the Timeslot is changed from an id to the date and the slot_id. This also leads to the id of the timeslot being removed.
In order to update the current master database, the following code can be executed:
ALTER TABLE Availability
ADD COLUMN date date NOT NULL,
ADD COLUMN slot_id int NOT NULL,
DROP FOREIGN KEY Availability_Timeslot,
DROP PRIMARY KEY,
ADD PRIMARY KEY (netid,date,slot_id),
DROP COLUMN timeslot_id;
ALTER TABLE LabAssignment
ADD COLUMN date date NOT NULL,
ADD COLUMN slot_id int NOT NULL,
DROP FOREIGN KEY LabAssignment_Timeslot,
DROP PRIMARY KEY,
ADD PRIMARY KEY (netid,schedule_id,date,slot_id),
DROP COLUMN timeslot_id;
ALTER TABLE LabTimeslot
ADD COLUMN date date NOT NULL,
ADD COLUMN slot_id int NOT NULL,
DROP FOREIGN KEY LabTimeslot_Timeslot,
DROP PRIMARY KEY,
ADD PRIMARY KEY (lab_id,date,slot_id),
DROP COLUMN timeslot_id;
ALTER TABLE Timeslot
DROP PRIMARY KEY,
ADD PRIMARY KEY (date, slot_id),
DROP COLUMN id;
ALTER TABLE Availability ADD CONSTRAINT Availability_Timeslot FOREIGN KEY Availability_Timeslot (date,slot_id) REFERENCES Timeslot (date,slot_id);
ALTER TABLE LabAssignment ADD CONSTRAINT LabAssignment_Timeslot FOREIGN KEY LabAssignment_Timeslot (date,slot_id) REFERENCES Timeslot (date,slot_id);
ALTER TABLE LabTimeslot ADD CONSTRAINT LabTimeslot_Timeslot FOREIGN KEY LabTimeslot_Timeslot (date,slot_id) REFERENCES Timeslot (date,slot_id);
Edited by Otto Visser