外键作为主键
我设计了这样的表格:
table1: students --------------------- PK id name number ... --------------------- table2: students_score --------------------- PK FK student_id math_score english_score ... ---------------------
问题1
如果有些学生根本没有分数,这样的表格设计好吗?
问题2
如果设计得好,那么如何在MySQL中使FK成为PK?我不知道怎么办。每次我尝试建立像上面这样的关系时,SQLYog 都会显示此错误: Can't create table 'students.#sql-a31_2c8e' (errno: 150)
谢谢
更新
我发现问题 2 的答案 从这里。这只是类型(int,signed int)的问题。
I designed tables like this:
table1: students --------------------- PK id name number ... --------------------- table2: students_score --------------------- PK FK student_id math_score english_score ... ---------------------
Question 1
If some students doesn't have scores at all, is it good table design?
Question 2
If it's good design, then how can I make FK as PK in MySQL? I can't find out how. Everytime I try to make a relation like above SQLYog says this error: Can't create table 'students.#sql-a31_2c8e' (errno: 150)
Thanks
Update
I found an answer of the question 2 from here. This was just a problem of type(int, signed int).
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
我会建议更多这样的事情:
I would suggest something more along these lines:
请改用
UNIQUE
和FOREIGN KEY
。它将允许您将FOREIGN KEY
与students_score
表一起使用,并将student_id
列保持唯一。Use
UNIQUE
andFOREIGN KEY
instead. It will allow you to use theFOREIGN KEY
with yourstudents_score
table, and maintain thestudent_id
column as unique.如果有的学生根本没有分数,那么表格设计好不好呢?
不,如果某些学生没有分数,则 Students_score 表上不会(或不应该)有记录。但这不是一个好的设计,这就是你会出错的原因。
您的设计应该类似于:
考虑在
students_score
表上为您的 Student_id 创建一个UNIQUE
索引,但这会将每个学生的记录数限制为一条,这可能不是你想要的。If some students doesn't have scores at all, is it good table design?
No, if some students doesn't have scores, there won't be (or shouldn't be) records on the students_score table. It is not a good design though, and that's why you get errors.
Your design should be something similar to:
Consider creating an
UNIQUE
index for your student_id on thestudents_score
table, but that will limit your number of records per student to one, which maybe is not what you want.