mysql - Mutually dependent foreign keys

Question

I am trying to create a database, which has 2 tables with mutually dependent foreign keys.

First table is called questions, it holds data regarding questions that are asked by users, and also needs to hold a key to the best answer that was answered on the question. (This should be a foreign key to our second table called Answer)

Second table is called Answer, it holds information regarding answers to questions, and also needs to hold a question_id field, that is a key to the question that this answer answers. this is also a foreign key, to the first table.

When I try to create the tables, It cannot create the first one, since it is not aware of the second one (error when we try to declare the foreign key to the second table which does not exist yet)

Here is the code I am 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 Do i resolve this problem? Thanks

Answer 1

Create the first table without the foreign key constraint. Then create the 2nd table as-is. Finally, go back and alter the first table, adding the foreign key constraint seperately.

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 the question-to-answer relationship in both tables? Because (as ypercube points out) you don't have a "best answer" when the question is first asked, yet your design requires it. It's probably better to maintain that relationship in the answer table, similar to how Olivier recommended.