Skip to content

Change the Timeslot table Private Key

Otto Visser requested to merge backend_timeslot_pk into master

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

Merge request reports