MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE lectures (
  lectureId INT NOT NULL,
  title VARCHAR(10) NOT NULL,
  PRIMARY KEY (lectureId)
 );





CREATE TABLE `groups` (
  lectureId INT NOT NULL,
  groupNo INT NOT NULL,
  title VARCHAR(10) NOT NULL,
  PRIMARY KEY (lectureId,groupNo),
  FOREIGN KEY (lectureId) REFERENCES lectures (lectureId)
    ON UPDATE CASCADE ON DELETE CASCADE
 );
CREATE TABLE studentListed (
  studentId INT NOT NULL,
  lectureId INT NOT NULL,
  groupNo INT NULL,
  PRIMARY KEY (studentId,lectureId),
  #FOREIGN KEY (lectureId) REFERENCES lectures (lectureId) 
  #  ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (lectureId,groupNo) REFERENCES `groups` (lectureId,groupNo)
    ON UPDATE CASCADE ON DELETE CASCADE
 );
CREATE TRIGGER GroupDelete BEFORE DELETE ON `groups`
FOR EACH ROW
  UPDATE studentListed SET studentListed.groupNo = NULL
    WHERE studentListed.lectureId = OLD.lectureId
    AND studentListed.groupNo = OLD.groupNo;

INSERT INTO lectures
VALUES
(1, "lecture1");



INSERT INTO `groups`
VALUES
(1, 1, "group1");
INSERT INTO studentListed
VALUES
(1, 1, 1);
UPDATE lectures SET lectureId = 2 WHERE lectureId = 1
SELECT * FROM studentListed


studentId
lectureId
groupNo


1
2
1


UPDATE lectures SET lectureId=2 WHERE lectureId=1; /* Offending line */