CODEWITHSUNDEEP
mysqldatabase-designforeign-keys
happy dude
happy dude

Reputation: 123

mysql - Mutually dependent foreign keys

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

Upvotes: 9

Views: 5873

Answers (3)

Marcus Adams
Marcus Adams

Reputation: 53830

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.

Upvotes: 6

Aaron
Aaron

Reputation: 57748

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.

Upvotes: 7

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112279

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.

Upvotes: 3

Related Questions