1

I used the following syntax and keep getting the following error at the end. " Key column 'FK_UserQuestion' doesn't exist in table":

CREATE DATABASE QA;

CREATE TABLE QA.Users
(
UserID int AUTO_INCREMENT PRIMARY KEY,
fName varchar(150),
lname varchar(150),
isadmin bit
) engine = INNODB;

CREATE TABLE QA.Question
(
QuestionID int AUTO_INCREMENT PRIMARY KEY,
UserID int,
Question varchar(65535),
FOREIGN KEY (FK_UserQuestion, UserID)     
    REFERENCES QA.Users (UserID) 
) engine = INNODB;

CREATE TABLE QA.Answer
(
AnswerID int AUTO_INCREMENT PRIMARY KEY,
QuestionID int,
UserID int,
Answer BLOB(65535),
IsSolver bit,
FOREIGN KEY (User_ID, UserID)
    REFERENCES QA.Users(UserID)
FOREIGN KEY ( FK_AnswerQuestion,QuestionID)     
    REFERENCES QA.Question (QuestionID) 
) engine = INNODB;

I've tried to put out the "QA." from each table, and tried to run statements that are separated by semi-colon, each turn too, but nothing solved. Tried many things and nothing I did is correct for MySQL Workbench 6.3 CE.

1 Answer 1

4

Your foreign keys have the wrong format. The syntax should be:

FOREIGN KEY <name> (column_1, ..., column_n)
REFERENCES <parent table> (column_1, ..., columns_n)

In your case:

FOREIGN KEY FK_UserQuestion (UserID)
REFERENCES QA.Users (UserID)

I.e.

CREATE TABLE QA.Question
( QuestionID int AUTO_INCREMENT PRIMARY KEY
, UserID int
, Question varchar(65535)
, FOREIGN KEY FK_UserQuestion (UserID)     
  REFERENCES QA.Users (UserID) 
) engine = INNODB;

A similar change for QA.Answer should also be made

1
  • 1
    Thanks a lot @Lennart, I was stuck for months with this because I'm newbie at this and you made my project run! Thanks in advance and I wish a happy new year! Jan 9, 2017 at 10:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.