Using the same column in multiple foreign keys-CodePudding

My problem is very similar to this: Multiple Column Foreign Key: Set single column to Null "ON DELETE" instead of all

I will be using that problem and its code. The code in the answer is exactly the solution I am looking for, but there is an error.

A student might be listed for a lecture, and optionally for one of the lectures groups as well. If the lecture is deleted all student listing should be removed and all its groups. If a group is deleted, then the students should still be listed for the lecture, but they should not be assigned to a group anymore.

All of the above works. However, if I try to change the lecture id, I would expect the reference in studentListed and groups would be updated accordingly (On Update Cascade), but instead, it fails on this foreign key constraint for some reason:

#1452 - Cannot add or update a child row: a foreign key constraint fails
(`SomeDB`.`studentListed`, CONSTRAINT `studentListed_ibfk_1` FOREIGN KEY (`lectureId`)
REFERENCES `lectures` (`lectureId`) ON DELETE CASCADE ON UPDATE CASCADE)

Why does this happen? The cause seems to be that lectureId in studentListed is both a reference to lectureId in lectures and a reference to lectureId in groups which is itself a reference. Is there a better way to do this?

Sql code to reproduce:

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; /* Offending line */

CodePudding user response：

you can't change to lectureids 2 because none exists in the first place in the table

And the cascade works the other way

Further GROUPS os a reserved word in mysql, and should so be avoided.

Last i had to remove the second foreign key, as it already exists in groups and is so unnecessary, as the fpoeign key in groups in groups already chekcs if such a lectureid exists

EXAMPLE for the working of foreign keys

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 */

✓

✓

db<>fiddle here