9

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
happy dude
  • 113
  • 2
  • 5
  • 1
    See my answer in a similar question: [What is the best way to insert rows into tables with references 1 to 1 of each other?](http://stackoverflow.com/questions/6691366/mysql-database-design-inserting-rows-in-1to1-tables/6692054#6692054) – ypercubeᵀᴹ Jan 24 '12 at 17:21

3 Answers3

7

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.

Aaron
  • 55,518
  • 11
  • 116
  • 132
6

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

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'd ever have more than one best answer for a particular question (maybe a tie), add best_a_id column to the primary key also.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
3

Add a flag to the answer 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 the best_a_id from the question table.

APC
  • 144,005
  • 19
  • 170
  • 281
Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
  • This is a better approach but the tricky bit is to ensure that only one ANSWER is the best one for each QUESTION. I know how to do this with Oracle DBMS but I don't think MySQL supports function-based indexes, unique or otherwise. – APC Jan 24 '12 at 17:18
  • 3
    Not bad, but I'd rather have a best_answer table. – Marcus Adams Jan 24 '12 at 17:21
  • You could also use an AFTER UPDATE trigger to clear the flag of the other answers with the same question_id. – Olivier Jacot-Descombes Jan 24 '12 at 17:33