Mysql - Dependent Foreign Keys

Question

I am trying to create a database that has 2 tables with interdependent foreign keys.

The first table is called questions, it contains data on questions asked by users, and should also contain the key to the best answer to which the answer to the question was given. (This should be the foreign key for our second table, called the "Answer")

The second table is called the “Answer”, it contains information about the answers to the questions, and should also contain the question_id field, which is the key to the question to which this answer answers. it is also a foreign key to the first table.

When I try to create tables, it cannot create the first, since it does not know the second (error when we try to declare a foreign key to a second table that does not exist yet)

Here is the code I'm using:

create table question ( q_id numeric(10,0), best_a_id numeric(10,0), primary key(q_id), foreign key (best_a_id) references answer(a_id), ); create table answer ( a_id numeric(10,0), q_id numeric(10,0) not null, primary key(a_id), foreign key (q_id) references question(q_id), ); 

How to solve this problem? Thanks

Answer 1

Create the first table without foreign key constraint. Then create the second table as is. Finally, go back and change the first table, adding the foreign key constraint separately.

And the SQL to add the foreign key will look like this:

 ALTER TABLE question ADD FOREIGN KEY (best_a_id) REFERENCES answer(a_id); 

Just curious, but why maintain a question-answer relationship in both tables? Because (as ypercube points out) you don’t have a “better answer” when the question is asked first, but your design requires it. It is probably best to maintain these relationships in the answer table, similar to what Olivier recommended.

Answer 2

Consider getting rid of question.best_a_id and adding the best_answers table instead:

 create table best_answers ( q_id numeric(10,0), best_a_id numeric(10,0), primary key(q_id), foreign key (best_a_id, q_id) references answer(a_id, q_id) ); 

If you have more than one answer to a specific question (possibly a tie), add the best_a_id column to the primary key.

Answer 3

Add a flag to the response table.

 create table answer ( a_id numeric(10,0), q_id numeric(10,0) not null, best_answer numeric(1) default 0 not null, primary key(a_id), foreign key (q_id) references question(q_id), ); 

And remove best_a_id from the question table.