What is wrong with these tables?

Question

I'm trying to create these two tables:

CREATE TABLE Game_Reviews(   

PRIMARY KEY(game_review_id, game),
game_review_id int,
game int,
date_posted date,
content varchar(100),
verified_reviewer varchar(20) FOREIGN KEY REFERENCES Verified_Reviewers,
FOREIGN KEY(game) REFERENCES Games
)

CREATE TABLE Game_Review_Comments(

PRIMARY KEY(comment_id, game_review),
comment_id int,
game_review int FOREIGN KEY REFERENCES Game_Reviews,
member varchar(20) FOREIGN KEY REFERENCES Members

)

But I'm getting an error saying:

The number of columns in the referencing column list for foreign key 'FK__Game_Revi__game___2022C2A6' does not match those of the primary key in the referenced table 'Game_Reviews'.

Although I'm not sure, I have a feeling that this error is due to the fact that when I reference Game_Reviews in the second table it doesn't know which primary key to use. How can I fix this?

Possible duplicate of [Multiple-column foreign key in MySQL?](http://stackoverflow.com/questions/953035/multiple-column-foreign-key-in-mysql) — Cristik, Nov 10 '15 at 12:41
A FK must match a key (e.g. PK) column-wise, in numbers and data types. game_review int FOREIGN KEY REFERENCES Game_Reviews doesn't. — jarlh, Nov 10 '15 at 12:47
game_reviews has 2 columns as primary key while you only have 1 column mentioned in the foreign key — Thomas, Nov 10 '15 at 12:52

score 0 · Answer 1 · answered Nov 10 '15 at 12:51

The problem you are facing is all standing inside the error message:

The number of columns in the referencing column list for foreign key 'FK__Game_Revi__game___2022C2A6' does not match those of the primary key in the referenced table 'Game_Reviews'.

The problem is that your foreign key does not match your primary key. That is because your primary key in game_reviews consists of 2 different columns while your foreign key only consists of 1 column.

Thus you would have to create a combined foreign key in Game_Review_Comments which contains references to both game_review_id and game.

Although from how I understand your table structure I think you really should check again if you really need a combined primary key there and not just have game_review_id as id (I don't see any advantage there to have both columns as part of the primary key).

The same holds true for game_review_comments. Here you also have a combined primary key. Thus you will always need 2 columns to reference it as foreign key. And also here, I would say you should reconsider making it a 1 column primary key instead as it doesn't seem to hold any advantage to me to make it 2 columns (both tables).

What is wrong with these tables?

1 Answers1