CODEWITHSUNDEEP
mysql
do734
do734

Reputation: 37

SQL wont allow me to add foreign key?

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

Upvotes: 0

Views: 104

Answers (4)

w0051977
w0051977

Reputation: 15817

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

Upvotes: 1

Ravi
Ravi

Reputation: 31417

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.

Upvotes: 0

Ritesh Khatri
Ritesh Khatri

Reputation: 1301

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

Upvotes: 0

Venkata Rahul S
Venkata Rahul S

Reputation: 324

Two issues here:

  1. Your Questions table does not have a username column. It must. Even then it would not work because of problem 2.

  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:

Upvotes: 0

Related Questions