外键作为主键

发布于 2024-11-18 14:28:09 字数 651 浏览 5 评论 0原文

我设计了这样的表格:

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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

花开浅夏 2024-11-25 14:28:09

我会建议更多这样的事情:

table1: students
---------------------
PK id
name
number
...
---------------------

table3: classes
---------------------
pk id
name

table2: students_score
---------------------
fk student_id
fk class_id
score
PK(student_id, class_id)

I would suggest something more along these lines:

table1: students
---------------------
PK id
name
number
...
---------------------

table3: classes
---------------------
pk id
name

table2: students_score
---------------------
fk student_id
fk class_id
score
PK(student_id, class_id)
遇见了你 2024-11-25 14:28:09

请改用 UNIQUEFOREIGN KEY。它将允许您将 FOREIGN KEYstudents_score 表一起使用,并将 student_id 列保持唯一。

Use UNIQUE and FOREIGN KEY instead. It will allow you to use the FOREIGN KEY with your students_score table, and maintain the student_id column as unique.

揽月 2024-11-25 14:28:09

如果有的学生根本没有分数,那么表格设计好不好呢?

不,如果某些学生没有分数,则 Students_score 表上不会(或不应该)有记录。但这不是一个好的设计,这就是你会出错的原因。

您的设计应该类似于:

students
---------------------
PK id
name
number

students_score
---------------------
FK student_id
math_score
english_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:

students
---------------------
PK id
name
number

students_score
---------------------
FK student_id
math_score
english_score
...

Consider creating an UNIQUE index for your student_id on the students_score table, but that will limit your number of records per student to one, which maybe is not what you want.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文