SQL wont allow me to add foreign key?

Question

I'm building a question and answer site and for linking the user accounts to the questions I want to have the username from the login table be a foreign key of the questions table so when a user asks a question it must be linked to his or her username. Can I not have foreign key's that are varchar datatype? Or must the foreign key be only a primary key of another table? The error I'm getting says 'Key column "'username' doesn't exist in table" . What am I doing wrong?

create table login (user_id int NOT NULL AUTO_INCREMENT, username 
varchar (100), password varchar (100), primary key (user_id));

create table questions (q_id int not null auto_increment, question 
varchar (100), primary key (q_id), foreign key (username) references 
login (username));

w0051977 · Accepted Answer · 2017-07-08T15:52:51.583

1

I think this is what you want:

create table login (username 
varchar (100), password varchar (100), primary key (username));

create table questions (q_id int not null auto_increment, question 
varchar (100), primary key (q_id), username varchar(100),foreign key (username) references 
login (username)); 

create table answers (a_id int not null auto_increment, answer varchar (100), primary key (a_id), q_id int, foreign key (q_id) references questions (q_id));

I tested it here: http://sqlfiddle.com/#!9/61ec2a

You need to:

1) Make Login.UserName unique
2) Declare UserName in the questions table

edited Jul 08 '17 at 15:52

answered Jul 08 '17 at 15:15

w0051977

15,099
32
152
329

yes thank you I think this solve the problem because I also have an answer table that the q_id is a foreign key of. Appreciate the help! – do734 Jul 08 '17 at 15:23
Don't forget to mark the question as answered if it has helped you the most. Let me know if anything needs to be clarified. – w0051977 Jul 08 '17 at 15:24
I'm new to this site and would give you a point if I could but they wont let me. I'll figure out how to say answerd though becasue this did work and I thank you! one more question though. now I'm trying to get my answers table but it's not working. It worked when I did it like this before but not now. see anything wrong? – do734 Jul 08 '17 at 15:43
create table answers (a_id int not null auto_increment, answer varchar (100), primary key (a_id), foreign key (q_id) references questions (q_id)); – do734 Jul 08 '17 at 15:43
@do734, I have added your answers table to my answer. Let me know it works - I was unable to test it as SQLFiddle appears to be down. – w0051977 Jul 08 '17 at 15:53
Sorry was away and just got back. It works! Thanks a lot! – do734 Jul 08 '17 at 22:13

Ravi · Answer 2 · 2017-07-08T15:20:24.920

0

Remember, Foreign Key is a Key and should expect to be linked with another Key. Now, username is simple column in one table, then how it can be key column for another table ?

The purpose of Foreign key is to show the relationship between the tables. You MUST have same column in both the table, which you are going relate.

edited Jul 08 '17 at 15:20

answered Jul 08 '17 at 15:11

Ravi

30,829
42
119
173

Ok thank you. So if I'm understanding correctly a foreign key needs to be linked to a primary key only meaning I should take a different approach to linking the username and q_id right? Or is there a way to do what I'm attempting to do? – do734 Jul 08 '17 at 15:15
No, It must link with **key** column and that can be even **unique key** – Ravi Jul 08 '17 at 15:16

score 0 · Answer 3 · answered Jul 08 '17 at 15:14

Two issues here:

Your Questions table does not have a username column. It must. Even then it would not work because of problem 2.
As a general rule, your foreign key reference needs to use the user_id and not the username. The reason is that Fk references generally start from a column and end in a primary key value.

create table questions (q_id int not null auto_increment, user_id int not null, question varchar (100), primary key (q_id), foreign key (user_id) references login (user_id));

Here is a more elaborate elaboration from a related question:

score 0 · Answer 4 · answered Jul 08 '17 at 15:16

The correct way is,

create table login (user_id int NOT NULL AUTO_INCREMENT, username 
varchar (100), password varchar (100), primary key (user_id));

create table questions (q_id int not null auto_increment, question 
varchar (100), primary key (q_id), foreign key (user_id) references 
login (user_id));

SQL wont allow me to add foreign key?

4 Answers4