MySQL能否利用外鍵關聯表單的欄位建立索引

mysql 外鍵索引

迷路 2021-07-16 16:19:03 ‧ 1211 瀏覽

分享至

原本有四張表
student表，主鍵student_id和其他欄位
question表，主鍵question_id和其他欄位
answer表，主鍵answer_id，question_id和其他欄位
student_qa表，student_id和question_id複合主鍵和answer_id
由於answer和question是有對應關係的
所以理論上student_qa只需要紀錄student_id和answer_id即可，但是需要唯一索引的欄位卻是student_id和question_id，目前是紀錄三個欄位設定索引，然後用程式確保answer_id和question_id的對應是正確的
想要問的是
如果student_qa的answer_id欄位設為外鍵關連到answer，student_qa是否能用自身的student_id欄位和answer表的question_id建立索引，這樣就可以避免自身紀錄的question_id和answer_id兜不起來的情況

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

海綿寶寶

iT邦大神 1 級 ‧ 2021-07-17 09:59:13

最佳解答

Table 的設計要以「需求」為出發點考量
就是「要查詢那些資訊」？例如：
1.學生問了那些問題
2.學生回答了那些問題
3.問題有那些答案

根據你的文字描述製圖如下

坦白說我看不太懂,例如：
1.student_id 和 question_id 和 answer_id 的關係是什麼？
2.會不會問題還沒有答案的情形？
3.一個問題由一個學生發問；答案需要記錄是那個學生回答的嗎？

我稍微改了一下如下

如果上圖可以滿足題意的所有需求
再來討論細節
不然就算我胡說八道
不用理我

另外提供今天才發現的好物
就是免費免註冊線上畫 ERD的服務
下面是產生上面兩張圖的 source

//// -- LEVEL 1
//// -- Tables and References

// Creating tables
Table student as s {
  student_id int [pk] // auto-increment
}

Table question as q {
  question_id int [pk]
 }

Table answer as a {
  answer_id int [pk]
  question_id int
 }

Table student_qa as qa {
  student_id int [pk]
  question_id int [pk]
  answer_id int [pk]
 }

Ref: "student_qa"."answer_id" < "answer"."answer_id"

//// -- LEVEL 1
//// -- Tables and References

// Creating tables
Table student as s {
  student_id int [pk, increment] // auto-increment
}

Table question as q {
  question_id int [pk]
  student_id int 
 }

Table answer as a {
  answer_id int [pk]
  student_id int
 }

//// -- LEVEL 2

Table question_answer as qa {
  question_id int [pk]
  answer_id int [pk]
 }



// Creating references
// You can also define relaionship separately
// > many-to-one; < one-to-many; - one-to-one
Ref: s.student_id < q.student_id
Ref: s.student_id < a.student_id

Ref: qa.question_id - q.question_id
Ref: qa.answer_id - a.answer_id

回應 3
分享
檢舉

迷路 iT邦新手 2 級 ‧ 2021-07-19 08:31:52 檢舉

question這張表是紀錄選擇題的問題
answer這張表是紀錄選擇題的選項，所以表中的question_id是關連到question的主鍵
student_qa這張表是紀錄學生選擇的答案
在原本的設計中question_id關連到question的主鍵，answer_id關連到answer的主鍵
由於學生不能重複回答相同問題，所以student_id和question_id聯合組成student_qa的主鍵
但是如果student_qa.question_id和student_qa.answer_id對應到的answer.question_id不相等的話，就會出現錯誤，原本是靠著程式碼避免這樣的問題
現在想問的是，能否將student_qa的question_id省去後，將answer_id設定為外鍵關連到awser的主鍵，在利用這層關係將answer.question_id欄位抓過來student_qa當複合主鍵的欄位之一

海綿寶寶 iT邦大神 1 級 ‧ 2021-07-19 08:41:42 檢舉

我覺得答案是「不行」
看看別的高手有沒有辦法

迷路 iT邦新手 2 級 ‧ 2021-07-22 13:52:12 檢舉

想到另一個方式
將answer的question_id和answer_id建立unique索引
student_qa維持三個欄位不變，將question_id和answer_id設為複合外鍵，關連到answer的對應欄位
這樣一來，如果answer中不存在question_id和answer_id的組合資料，student_qa就無法新增，應該可以保證資料的正確性