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:

studyLkup with StudyID as primary key
interviewerLkup with InterviewerID as primary key
studyInterviewers with StudyID and InterviewerID as primary key
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;

score 0 · Answer 1 · answered Oct 07 '14 at 17:59

0

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

answered Oct 07 '14 at 17:59

user254153

1,855
4
41
84

i only want to delete from the studyInterviewers table. it shouldn't affect the participant table because the interviewer is not used for that particular study (but may be used in other studies). i do not want to delete the interviewer from the lookup table either. – ajwong4 Oct 07 '14 at 18:03
http://stackoverflow.com/questions/439750/t-sql-selecting-rows-to-delete-via-joins Follow these – user254153 Oct 07 '14 at 18:10
do i need a join if i'm only deleting from one table? the participant table should not have any rows for the specific combination of study and interviewer. the delete function is there in case someone accidentally adds an interviewer to the wrong study. – ajwong4 Oct 07 '14 at 19:15
could you give sql code in your answer? i'm a sql newbie. thanks! – ajwong4 Oct 07 '14 at 19:22
note that i am trying to delete a specific tuple, not a group of tuples that meet a certain condition. – ajwong4 Oct 08 '14 at 17:14

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)

1 Answers1