mysql - взаимозависимые внешние ключи

Я пытаюсь создать базу данных, в которой есть 2 таблицы с взаимозависимыми внешними ключами.

Первая таблица называется вопросами, она содержит данные о вопросах, которые задают пользователи, а также должна содержать ключ к наилучшему ответу, на который был дан ответ на вопрос. (Это должен быть внешний ключ для нашей второй таблицы под названием Ответ)

Вторая таблица называется «Ответ», она содержит информацию об ответах на вопросы, а также должна содержать поле question_id, которое является ключом к вопросу, на который отвечает этот ответ. это также внешний ключ к первой таблице.

Когда я пытаюсь создать таблицы, он не может создать первую, поскольку не знает о второй (ошибка, когда мы пытаемся объявить внешний ключ для второй таблицы, которая еще не существует)

Вот код, который я использую:

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

Как мне решить эту проблему? Спасибо


mysql foreign-keys database-design
person happy dude    schedule 24.01.2012    source источник
comment
См. Мой ответ в аналогичном вопросе: Что лучше как вставить строки в таблицы со ссылками 1 на 1 друг на друга?   -  person ypercubeᵀᴹ    schedule 24.01.2012

Ответы (3)


arrow_upward
7
arrow_downward

Создайте первую таблицу без ограничения внешнего ключа. Затем создайте вторую таблицу как есть. Наконец, вернитесь и измените первую таблицу, отдельно добавив ограничение внешнего ключа.

И SQL для добавления внешнего ключа будет выглядеть так:

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

Просто любопытно, но зачем поддерживать соотношение вопрос-ответ в обеих таблицах? Потому что (как указывает ypercube) у вас нет «лучшего ответа», когда вопрос задается впервые, но ваш дизайн требует этого. Вероятно, лучше сохранить эту взаимосвязь в таблице ответов, подобно тому, как рекомендовал Оливье.

person Aaron    schedule 24.01.2012
comment
А затем, как вы будете вставлять строки? - person ypercubeᵀᴹ; 24.01.2012
comment
@happy dude: Это может сработать, только если question.best_a_id установлено как Nullable. - person ypercubeᵀᴹ; 25.01.2012
comment
Это будет работать, пока вы не попытаетесь удалить одну из строк. Ограничения внешнего ключа будут сталкиваться ... взаимно, и они не позволят удалить / удалить ни то, ни другое. - person Rican7; 12.09.2013

arrow_upward
6
arrow_downward

Попробуйте избавиться от question.best_a_id и вместо этого добавить таблицу best_answers:

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

Если у вас когда-либо будет более одного лучшего ответа на конкретный вопрос (возможно, ничья), добавьте также столбец best_a_id к первичному ключу.

person Marcus Adams    schedule 24.01.2012
comment
В MySQL также потребуется UNIQUE KEY в таблице answer на (a_id, q_id). - person ypercubeᵀᴹ; 24.01.2012

arrow_upward
3
arrow_downward

Добавьте отметку в таблицу ответов.

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

И удалите best_a_id из таблицы вопросов.

person Olivier Jacot-Descombes    schedule 24.01.2012
comment
Это лучший подход, но сложность заключается в том, чтобы гарантировать, что только один ОТВЕТ является лучшим для каждого ВОПРОСА. Я знаю, как это сделать с СУБД Oracle, но я не думаю, что MySQL поддерживает функциональные индексы, уникальные или другие. - person APC; 24.01.2012
comment
Неплохо, но я бы предпочел таблицу best_answer. - person Marcus Adams; 24.01.2012
comment
Вы также можете использовать триггер AFTER UPDATE, чтобы снять отметку с других ответов с тем же question_id. - person Olivier Jacot-Descombes; 24.01.2012