Search code examples
mysqldatabase-designforeign-keysprimary-keysqlyog

Foreign key as Primary key


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).


Solution

  • 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)