Can't delete tuple in a relationship table when part of the tuple is used in another table (but whole tuple is not in that other table)

Question

I have 4 tables:

1. studyLkup with StudyID as primary key
   
2. interviewerLkup with InterviewerID as primary key
   
3. studyInterviewers with StudyID and InterviewerID as primary key
   
4. participant with participantID as primary key and StudyID and InterviewerID as foreign keys referencing the studyLkup and studyInterviewers tables respectively.
   

I want to be able to delete a study/interviewer relationhip if the interviewer is not used in a particular study. I do not want to delete the interviewer out of the interviewerLkup table, just from the studyInterviewers table.

The problem I am having is that if the interviewer is used at all in the participant table (for another study), it won't let me delete the study/interviewer relationship.

Note that I want to delete a specific tuple. I do not want to delete all study/interviewers that are not used in the participant table. I am using the deletion process to delete a specific interviewer that has been incorrectly assigned to a particular study.

Can someone explain how to do this?

The php/mySql code I'm unsuccessfully using is:

    $sql1 = "DELETE FROM studyinterviewers
             WHERE (StudyID = '".$StudyID."')
                AND (InterviewerID = '".$InterviewerID."');";

sql code for this mini schema is given below. Have I set my foreign keys in the participant table correctly?

    CREATE TABLE IF NOT EXISTS `interviewcodes`.`studylkup` (
      `StudyID` INT(11) NOT NULL AUTO_INCREMENT,
      `StudyName` VARCHAR(45) NOT NULL,
      PRIMARY KEY (`StudyID`))
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8;

    CREATE TABLE IF NOT EXISTS `interviewcodes`.`interviewerlkup` (
      `InterviewerID` INT(11) NOT NULL AUTO_INCREMENT,
      `InterviewerFirstName` VARCHAR(45) NOT NULL,
      `InterviewerLastName` VARCHAR(45) NOT NULL,
      PRIMARY KEY (`InterviewerID`))
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8;

    CREATE TABLE IF NOT EXISTS `interviewcodes`.`studyinterviewers` (
      `StudyID` INT(11) NOT NULL,
      `InterviewerID` INT(11) NOT NULL,
      PRIMARY KEY (`StudyID`, `InterviewerID`),
      INDEX `fk_StudyInterviewers_InterviewerLkup1_idx` (`InterviewerID` ASC),
      CONSTRAINT `fk_StudyInterviewers_InterviewerLkup1`
        FOREIGN KEY (`InterviewerID`)
        REFERENCES `interviewcodes`.`interviewerlkup` (`InterviewerID`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_StudyInterviewers_StudyLkup1`
        FOREIGN KEY (`StudyID`)
        REFERENCES `interviewcodes`.`studylkup` (`StudyID`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8;

    CREATE TABLE IF NOT EXISTS `interviewcodes`.`participant` (
      `ParticipantID` INT(11) NOT NULL AUTO_INCREMENT,
      `ParticipantCaseID` VARCHAR(45) NOT NULL,
      `StudyID` INT(11) NOT NULL,
      `InterviewerID` INT(11) NOT NULL,
      PRIMARY KEY (`ParticipantID`),
      INDEX `fk_participant_studyinterviewers1_idx` (`InterviewerID` ASC),
      CONSTRAINT `fk_participant_studyinterviewers1`
        FOREIGN KEY (`InterviewerID`)
        REFERENCES `interviewcodes`.`studyinterviewers` (`InterviewerID`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_participant_studylkup1`
        FOREIGN KEY (`StudyID`)
        REFERENCES `interviewcodes`.`studylkup` (`StudyID`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8;

Answer 1

You can use inner join to delete from more than one table.