1

Question

Is the set up for foreign keys I want to do for Answer Table the correct Idea and how to write SQL on Add foreign key constraint dealing with multiple fields?

Session Table:

SessionId (PK) SessionName
1              AAA
2              AAB

Question Table:

QuestionId(PK)  SessionId(PK)  QuestionContent
1               1              What is 2+2?
2               1              What is 3+3?
1               2              What is 4+4?
2               2              What is 5+5?

Answer Table:

AnswerId(auto, PK)  QuestionId(fk)  SessionId(fk)  Answer
1                   1               1              A
2                   1               1              D
3                   2               1              C
4                   2               1              A
5                   1               2              True
6                   2               2              A
7                   2               2              B

Now the issue I have is with foreign keys with the Answer Table, I want to know which foreign key is correct to do.

What I have done is that I add a foreign key to the SessionId which means that if I click on a row's SessionId, it matches the SessionId in the Session Table.

Then I thought I will provide a foreign key on QuestionId in Answer Table linking to Question Table. Problem with this though is that if within a row I click on a QuestionId, then it will display me all of the rows in the QuestionId which contains the same QuestionId. So if I wanted to look up this Answer:

AnswerId(auto, PK)  QuestionId(fk)  SessionId(fk)  Answer
1                   1               1              A

When I click on QuestionId Foreign key above it outputs this below:

QuestionId(PK)  SessionId(PK)  QuestionContent
1               1              What is 2+2?
1               2              What is 4+4?

The Answer does not belong to both of these questions, it only belongs to one question but because both QuestionId are the same, it displays both questions.

So what I am thinking is that I want a foreign key constraint where that if user clicks on the QuestionId in a row, it will look up both QuestionId and SessionId so that it knows that question and session (exam) that answer belongs to so that it outputs below:

QuestionId(PK)  SessionId(PK)  QuestionContent
1               1              What is 2+2?

My question is that I don't know how to write a foreign key constraint which deals with two fields? Also is the set up of foreign keys for just SessionId and SessionId and QuestionId together correct?

saravankg
  • 909
  • 1
  • 10
  • 21
Manixman
  • 307
  • 6
  • 19

2 Answers2

3

You can make a composite foreign key for your Answers table using this syntax:

FOREIGN KEY (QuestionId, SessionId) REFERENCES Questions (QuestionId, SessionId)

However, it seems strange that your SessionId column for the Questions table is part of a composite Primary Key, considering you have a Sessions table using a column by that name as the Primary Key. You should probably consider making the Questions.SessionId a foreign key on your Sessions table, and have QuestionId be a standard single Primary Key.

If you do this, you'll find that you won't need a SessionId column on your Answers table.

Here's an example of what you might do:

-- Sessions table
SessionId (PK) | SessionName

-- Questions table
QuestionId (PK) | SessionId (FK) | QuestionContent

-- Answers table
AnswerId (PK) | QuestionId (FK) | Answer
PinnyM
  • 35,165
  • 3
  • 73
  • 81
  • If I just make `QuestionId` a primary key then I will get duplicates. I know what you gonna say, why not make `QuestionId` auto and store it in a new field know as `QuestionNo`, the reason for that is that I wanted to use `QuestonId` in Answer table as you can see so we can determine the question the answer belongs to as well as the exam the answer belongs to – Manixman Jan 06 '13 at 02:42
  • If you want to show the way you will structure the three tables in your way then I will have a look, I just always thought of doing it the way I have done it but I know you have got more experience and more knowledge with this – Manixman Jan 06 '13 at 02:43
  • Updated with an example of how this might look. – PinnyM Jan 06 '13 at 02:46
  • Ok I will change it and make QuestionId auto and include a QuestionNo filed for question numbers, then I just need to change my whre clause in my queries that found question numbers using `QuestionId` to `QuestionNo` and displaying the `QuestionId` that belongs to that `QuestionNo` and `SessionId`. Thanks :) – Manixman Jan 06 '13 at 02:52
  • Really? This was my exact solution answered before this one? Nonetheless, good luck... – sgeddes Jan 06 '13 at 02:58
2

You are correct, in your current schema, you'd want to have 2 indexes on your Answer table; 1 on the SessionId column; and 1 on the SessionId and QuestionId columns. Here's a post on how to do it:

Multiple-column foreign key in MySQL?

On a side note, I might would consider making the QuestionId an identity/auto as well. Not sure you receive any added value making it reset per session. Could help simplify some of your required indexes.

Community
  • 1
  • 1
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • The questionId is really my question number, the values of the question number for each question is stored in questionId column. The sessionId is the id of an exam. So the required index I think is best for the `Answer` Table is to know which question does an answer belong to. Hence why we need a constraint on knowing exam and question number an answer belongs to – Manixman Jan 06 '13 at 02:38
  • Good luck Manixman -- not sure why you didn't accept my exact same answer though... – sgeddes Jan 06 '13 at 03:05
  • I know you answered the question first but he did ensure me to change my database design. My question was to also say if my schema was correct but as PinnyM mentioned it is better if I changed it to the schema he stated and then I don't need the multiple column foreign key. I have upvoted your answer though to give you some reputation points. – Manixman Jan 06 '13 at 03:13