CODEWITHSUNDEEP
mysqlsql
Lucky
Lucky

Reputation: 187

Foreign key in SQL

I have a table :

PROFESSOR(pid, pname)

and another table: 

EXAM(examid, professorID, assistantID , examinerID, subject);

I want to reference professorID,assistantID and examinerID to pid. A professor can be an assistant in an exam and be examiner and also the subject belongs to a professor so professors , assistants and examiners in EXAM table are selected from professors in PROFESSOR table.

I think the schema I have designed is wrong , is it? If its wrong how tho change it so that I can meet the condition I explained above.

Can you help please?

Upvotes: 2

Views: 110

Answers (5)

Lucky
Lucky

Reputation: 187

Thank you all, I wanted assistantID, professorID and examinerID to refrence PID. I was using Navicat to design tables visually and it was not working properly. I tested the same thing in sqlserver and mysql (using workbench) and bingo it worked .

CREATE TABLE `exam` (
  `examID` int(11) NOT NULL,
  `pID` int(11) DEFAULT NULL,
  `assistantID` int(11) DEFAULT NULL,
  `examinerID` int(11) DEFAULT NULL,
  PRIMARY KEY (`examID`),
  KEY `professor` (`pID`),
  KEY `assistant` (`assistantID`),
  KEY `examiner` (`examinerID`),
  CONSTRAINT `assistant` FOREIGN KEY (`assistantID`) REFERENCES `professor` (`pid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `examiner` FOREIGN KEY (`examinerID`) REFERENCES `professor` (`pid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `professor` FOREIGN KEY (`pID`) REFERENCES `professor` (`pid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

for now I just omitted subject because it needs a better schema design.

Upvotes: 0

Christopher Pelayo
Christopher Pelayo

Reputation: 802

I think the schema is enough already base on the relationship that he needs ("A professor can be an assistant in an exam and be examiner"). He could then directly use the pid on the columns professorID, assistanID and examinerID, but however the subject should be a separate table by then instead but somehow not sure of the other details that you need for the professor table there would be more details on the actual implementation for this but anyway the important thing is that only a few tweek and you'll have a good relationship on the go. :)

Upvotes: 0

John Woo
John Woo

Reputation: 263693

you can:

SELECT
       `exam`.`examID`
    , COALESCE(`professor`.`pName`,'') AS ProfessorName
    , COALESCE(`professor_2`.`pName`,'') AS AssistantName
    ,  COALESCE(`professor_1`.`pName`, '') AS ExaminerName
    , `exam`.`iSubject`
FROM
    `sampledb`.`exam`
    LEFT JOIN `sampledb`.`professor` 
        ON (`exam`.`professorID` = `professor`.`pID`)
    LEFT JOIN `sampledb`.`professor` AS `professor_1`
        ON (`exam`.`assistantID` = `professor_1`.`pID`)
    LEFT JOIN `sampledb`.`professor` AS `professor_2`
        ON (`exam`.`examinerID` = `professor_2`.`pID`);

Upvotes: 0

Amit
Amit

Reputation: 13364

I think the schema can be redesigned. You can have three tables:-

Professor(pid, pname, pAddr);

Exam(examId, subjectId, ...,..,assistantId,ExaminerId); (assistantId and ExaminerId referencing pId)

Subject(subjectId, desc, course, professorId); (professorId referencing pId)

Upvotes: 1

Muhammad Imran Tariq
Muhammad Imran Tariq

Reputation: 23352

Your schema is wrong.

You should define three tables. Professor,Exam and subject.

For assistant and examiner you should define a column in professor table 'role'. Role column will define whether professor is professor, examiner or assistant. Professor table foreign key will go in both of the other tables. i.e. subject and exam.

I think it will help you a lot for making your tables.

Upvotes: 0

Related Questions