Intereting Posts
Группировать по дате по интервалу недель / месяцев Специальные запросы и хранимые процедуры vs Dynamic SQL Выберите различные текущие и предыдущие столбцы из таблицы sql Получить IDataReader из типизированного списка MySQL: Почему процедура медленнее, чем выполнение запроса? Передавать пользовательский тип таблицы из VBA в SQL Дизайн таблицы счетов SQL: выберите строки в порядке убывания по столбцу2 и покажите только 1 результат, когда столбец 1 не равен NULL mongoose поддерживает виртуальные поля в select like SQL Поддержка SQL SQL Query проверка наличия всех элементов в списке в другом списке с использованием linq Требуется ли ключевое слово 'as' в Oracle для определения псевдонима? TSQL – вставить строку в целое число или вернуть значение по умолчанию Метод поиска Rails: выберите псевдоним как id? Можно ли переместить запись из одной таблицы в другую с помощью одного оператора SQL?

Как бороться с взаимно рекурсивными вставками

У меня есть модель, которая определяет взаимно-рекурсивные таблицы:

Answer questionId QuestionId text Question text correct AnswerId 

Что мне нужно сделать, чтобы действительно вставить вопрос? Мне нужно знать, какой правильный ответ в первую очередь. Но чтобы вставить ответ, мне нужно знать, на какой вопрос он отвечает.

Я запускаю Postgres, если это имеет значение.

DDL:

 CREATE TABLE answer ( id integer NOT NULL, -- answer id text character varying NOT NULL, -- answer text question_id bigint NOT NULL -- question id ); CREATE TABLE question ( id integer NOT NULL, -- question id question character varying NOT NULL, -- question text correct bigint NOT NULL, -- correct answer solution character varying NOT NULL -- solution text ); ALTER TABLE ONLY answer ALTER COLUMN id SET DEFAULT nextval('answer_id_seq'::regclass); ALTER TABLE ONLY answer ADD CONSTRAINT answer_question_id_fkey FOREIGN KEY (question_id) REFERENCES question(id); ALTER TABLE ONLY question ALTER COLUMN id SET DEFAULT nextval('question_id_seq'::regclass); ALTER TABLE ONLY question ADD CONSTRAINT question_correct_fkey FOREIGN KEY (correct) REFERENCES answer(id); 

Если вы вводите вопрос и ответ в одном выражении с помощью CTE , изменяющей данные , вам даже не нужны ограничения DEFERRABLE FK. Не говоря уже о том, чтобы на самом деле делать (или SET ting) их DEFERRED – это было бы намного дороже.

Модель данных

Сначала я очистил вашу модель данных:

 CREATE TABLE question ( question_id serial PRIMARY KEY , correct_answer_id int NOT NULL , question text NOT NULL , solution text NOT NULL ); CREATE TABLE answer ( answer_id serial PRIMARY KEY , question_id int NOT NULL REFERENCES question , answer text NOT NULL ); ALTER TABLE question ADD CONSTRAINT question_correct_answer_id_fkey FOREIGN KEY (correct_answer_id) REFERENCES answer(answer_id); 
  • Не используйте без описательного «id» в качестве имени столбца.
  • Не используйте имена базового типа, такие как text как имя столбца.
  • Сначала поставьте целые столбцы для эффективности пространства:
    • Вычисление и сохранение пространства в PostgreSQL
  • bigint был запрограммирован, integer должно хватить.
  • Упростите определение схемы с помощью serial столбцов .
  • Определите первичные ключи. Столбцы PK не являются NOT NULL автоматически.

Решение

Я делегировал генерацию первичного ключа в последовательности ( serial столбцы), как это должно быть в большинстве моделей данных. Мы можем получить автогенерированный идентификатор с предложением RETURNING INSERT . Но в этом специальном случае нам нужны оба идентификатора для каждого INSERT , поэтому я получаю один из них с nextval() чтобы начать работу.

 WITH q AS ( INSERT INTO question (correct_answer_id, question, solution) VALUES (nextval('answer_answer_id_seq'), 'How?', 'DEFERRABLE FK & wCTE') RETURNING correct_answer_id, question_id ) INSERT INTO answer (answer_id, question_id, answer) SELECT correct_answer_id, question_id, 'Use DEFERRABLE FK & data-modifying CTE' FROM q; 

Я знаю название последовательности ( 'answer_answer_id_seq' ), потому что я искал ее. Это имя по умолчанию. Если вы этого не знаете, используйте безопасную форму @IMSoP, указанную в комментарии ниже:

 nextval(pg_get_serial_sequence('answer', 'answer_id')) 

DEFERRABLE или DEFERRABLE ограничения?

По документации по SET CONSTRAINTS

Ограничения IMMEDIATE проверяются в конце каждого оператора.

Мое решение – это одно утверждение. Вот почему он работает, когда два отдельных оператора не работают – завернуты в одну транзакцию или нет. И вам понадобится SET CONSTRAINTS ... DEFERRED; как первый комментарий IMSoP и @Jaaz, реализованный в его ответе .
Однако обратите внимание на отказ от некоторых абзацев:

Ограничения уникальности и исключения, которые не были объявлены DEFERRABLE , также проверяются немедленно.

Поэтому UNIQUE и EXCLUDE должны быть DEFERRALBE чтобы заставить wCTE работать для них. Это включает ограничения PRIMARY KEY . В документации по CREATE TABLE содержится более подробная информация :

Неотложные ограничения уникальности

Когда ограничение UNIQUE или PRIMARY KEY не откладывается, PostgreSQL проверяет уникальность сразу всякий раз, когда строка вставлена ​​или изменена. В стандарте SQL говорится, что уникальность должна выполняться только в конце инструкции; это имеет значение, когда, например, одна команда обновляет несколько значений ключа. Чтобы получить стандартное поведение, объявите ограничение как DEFERRABLE но не отсрочено (т.е. INITIALLY IMMEDIATE ). Имейте в виду, что это может быть значительно медленнее, чем немедленная проверка уникальности.

Мы подробно обсудили это в этом вопросе:

  • Ограничение определено ОПАСНОЕ ИНИЦИАЛИЗИРОВАННОЕ ПОСЛЕДУЮЩЕЕ ПОСЛЕДОВАТЕЛЬНО?

Я бы поставил под вопрос вопрос с нулевым правилом AnswerId. Затем я вставляю в «Ответ», и, наконец, я обновляю вопрос и задаю правильный ответ.

Я посмотрел вокруг, увидев DDL. Рассмотрим функцию для вашего вызова, чтобы вставить вопрос с правильным ответом, а другой – добавить (ложные) ответы на заданный вопрос. Структура первой функции позволяет приложению подобрать анонимную возвращенную запись для идентификатора questionID и использовать ее для последующих вызовов второй функции, чтобы добавить ложные ответы.

 CREATE FUNCTION newQuestion (questionText varchar, questionSolutionText varchar, answerText varchar, OUT questionID integer) AS $$ BEGIN START TRANSACTION; SET CONSTRAINTS question_correct_fkey DEFERRED; questionID := nextval('question_id_seq'); answerID := nextval('answer_id_seq'); INSERT INTO question (id, question, correct, solution) values (questionID, questionText, answerID, questionSolutionText); INSERT INTO answer (id, text, question_id) values (answerID, answerText, questionID); SET CONSTRAINTS question_correct_fkey IMMEDIATE; COMMIT TRANSACTION; END; $$ CREATE FUNCTION addFalseAnswer (questionID integer, answerText varchar) AS $$ BEGIN INSERT INTO answer (text, question_id) VALUES (answerText, questionID); END; $$ 

Я долго не писал SQL для PostGreSQL, поэтому надеюсь, что все в порядке. пожалуйста, дайте мне знать, если есть какие-либо проблемы.