Преобразование псевдокода в скрипт SQL

Итак, у меня есть две таблицы:

  1. Bookmarks имеет несколько столбцов [id и т. д.]
  2. Person_Bookmark имеет 2 столбца [personId, bookmarkId]

Bookmarks представляет собой ссылки на другие веб-сайты. Все действительные закладки имеют идентификатор. Таблица Person_Bookmark содержит группу personIds и их закладок, показанных как bookmarkId.

Вот мой псевдокод:

> let x = integer list of all bookmarkId's from Person_Bookmark
> 
> for each x  {   
>     if ('select * from 'Bookmarks' where 'id' = x returns 0 rows) {
>       delete from 'person_bookmark' where 'bookmarkId' = x
>     }
> }

Пожалуйста, посоветуйте мне, как преобразовать в SQL-скрипт Postgres [edit].


postgresql sql foreign-keys referential-integrity
person Kevin Meredith    schedule 17.11.2012    source источник
comment
Вы не указали целевой диалект SQL. Тем не менее, возможный дубликат stackoverflow.com/questions/859759/   -  person Jan    schedule 17.11.2012
comment
Для начала подумайте о наборах вместо алгоритмов :) Рекомендация книги: SQL-запросы для простых смертных   -  person paulsm4    schedule 17.11.2012
comment
Псевдокод — это хорошо, но вам действительно нужно указать, чего вы хотите достичь, на простом английском языке!   -  person Erwin Brandstetter    schedule 17.11.2012

Ответы (3)


arrow_upward
5
arrow_downward

@Jan уже упоминал внешние ключи, но его совет неполный.

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

ALTER TABLE person_bookmarks
ADD CONSTRAINT pb_fk FOREIGN KEY (bookmarkid) REFERENCES bookmarks (id)
ON UPDATE CASCADE
ON DELETE CASCADE;

  • Это позволяет использовать только те значения в person_bookmarks.bookmarkid, которые существуют в bookmarks.id.

  • ON UPDATE CASCADE изменяет соответствующие значения в person_bookmarks.bookmarkid при изменении записи в bookmarks.id

  • ON DELETE CASCADE удаляет соответствующие строки в person_bookmarks.bookmarkid при изменении записи в bookmarks.id.

Доступны и другие варианты, прочитайте руководство

Предложение ON DELETE CASCADE делает автоматически то, что вы пытаетесь исправить вручную. Прежде чем вы сможете добавить ограничение fk, вам придется исправить его вручную один раз:

DELETE FROM person_bookmarks pb
WHERE NOT EXISTS (SELECT 1 FROM bookmarks b WHERE b.id = pb.bookmarkid);
-- OR NOT EXISTS (SELECT 1 FROM persons p   WHERE p.id = pb.personid);

Удаляет все строки с несуществующим bookmarkid. Раскомментируйте последнюю строку, чтобы избавиться и от мертвецов.

person Erwin Brandstetter    schedule 17.11.2012
comment
Эрвин - спасибо за подробное объяснение. С точки зрения архитектуры, ведутся ли споры о том, следует ли использовать ограничения внешнего ключа вместо того, чтобы программное обеспечение отвечало за внесение необходимых изменений? - person Kevin Meredith; 19.11.2012
comment
Внешние ключи сохраняют ссылочную целостность при любых обстоятельствах, насколько это возможно. Они являются проверенным решением в основе RDBMS. Любое решение в клиентском ПО рано или поздно обречено на провал. Подумайте о нарушенных соединениях, параллелизме (условиях гонки) прямого доступа к базе данных в обход клиентского программного обеспечения. - person Erwin Brandstetter; 19.11.2012

arrow_upward
2
arrow_downward

Это работает в SQL Server - не уверен насчет MySQL...

delete pb
from
  person_bookmark pb
where not exists (select 1 from booksmarks b where b.id = pb.bookmarkid)
person Derek Kromm    schedule 17.11.2012
comment
Вопрос на самом деле помечен PostgreSQL. Но это стандартный SQL, и он действительно должен работать в любой СУБД. - person a_horse_with_no_name; 17.11.2012

arrow_upward
0
arrow_downward

Другая версия ответа @Derek:

DELETE FROM person_bookmark 
WHERE bookmarkid NOT IN (SELECT id FROM bookmarks)

Необходимость сделать это подразумевает, что у вас нет индексов внешнего ключа между вашими таблицами. Я настоятельно рекомендую вам сделать это. Недостаток (или особенность) заключается в том, что когда вы, например, удаляете person (я предполагаю, что эта таблица существует из вашего примера), вы должны сначала удалить все связанные данные, иначе сервер выдаст ошибку.

Что-то вроде этого:

DELETE FROM person_Bookmark WHERE personid = @personid
DELETE FROM person_SomeOtherTable WHERE personid = @personid
DELETE FROM person WHERE id = @personid

Однако преимущество заключается в том, что в вашей базе данных не будет потерянных строк, и вы не сможете ввести ошибочные данные по ошибке (сохраните bookmark вместо person, которого не существует).

person Jan    schedule 17.11.2012