Do I need a Unique-Composite Key on Composite-FK?

My website allows members to take surveys about articles.

Here is some sample data…

QUESTION

id    stem
---    --------------------------------------
1    Where have you made friends?
2    What colors do you like?
ANSWER

id    question_id    choice
---    ------------    ---------
1    2        Work
2    2        Church
3    2        Other
4    6        Red
5    6        Green
6    6        Blue
7    6        Yellow

I have a junction table called “t_survey_multiple_response” which ties together ARTICLES, MEMBERS, QUESTIONS and ANSWERS.

I am trying to ensure that any record in this junction table has valid Question-Answer pairs, e.g. “Yellow” (id=6) would not be a valid response to Question #1.

Here are my table designs…

t_survey_answer

CREATE TABLE `t_survey_answer` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '(PK)',
  `question_id` mediumint(8) unsigned NOT NULL COMMENT '(UK1)(FK)',
  `choice` varchar(128) NOT NULL COMMENT '(UK2)',
  `sort` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1

t_survey_multiple_response

CREATE TABLE `t_survey_multiple_response` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '(PK)',
  `article_id` mediumint(8) unsigned NOT NULL COMMENT '(UK1)(FK)',
  `survey_question_id` mediumint(8) unsigned NOT NULL COMMENT '(UK2)(FK)',
  `survey_answer_id` mediumint(8) unsigned NOT NULL COMMENT '(UK3)(FK)',
  `member_id` mediumint(8) unsigned NOT NULL COMMENT '(UK4)(FK)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

So, I am trying to create a multi-field Foreign-Key Constraint, and it appears that MySQL requires an index on the related fields.

QUESTION:
In order to achieve my goal of making sure only valid Question-Answer keys can be inserted into the “t_survey_multiple_response” table, must my Composite-Keys in the “t_survey_multiple_response” and the “t_survey_answer” tables be UNIQUE??

Hope that makes sense?!

Sincerely,

Debbie

short answer: yes, in general, but not sure about your specific example

FK are required to reference either a PK or a UNIQUE key

i can’t comment on your attempted FK because i can’t see what you tried

So what can I provide to help you comment more?

When I was playing around with this last night - and you should be proud of my Rudy, because I was writing my first ALTER statements outside of phpMyAdmin - it seemed that I could create the FK Constraint with no Indexes, and then phpMyAdmin apparently created a NON-Unique one for me…

Intuitively, I would think I need this…

ALTER TABLE t_survey_multiple_response
ADD UNIQUE tsmr_u_question_answer (survey_question_id, survey_answer_id);
ALTER TABLE t_survey_answer
ADD UNIQUE tsa_u_id_questionid (id, question_id)

Sincerely,

Debbie