Постгрес SQL. Обновить соединительную таблицу
Я хочу обновить строку моей соединительной таблицы. Не буквально обновлять, вставлять только новые кортежи и удалять те, которые не были вставлены.
Вот мои таблицы:
таблица пользователей
-----------------------
|user_id |FullName |
-----------------------
|1 | John |
|2 | Michael |
|3 | Bryce |
таблица адресов
-----------------------------------------
|address_id| country | city |
-----------------------------------------
| 1 | USA | New-York |
| 2 | Russia | Moscow |
| 3 | Germany | Berlin |
| 4 | UK | London |
Это таблица соединений для соединения два сейчас.
"адрес_пользователя"
------------------------
| user_id | address_id |
------------------------
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
Например, я хочу вставить новые значения в свое соединение и сделать это так:
------------------------
| user_id | address_id |
------------------------
| 1 | 1 |
| 1 | 3 |
| 2 | 4 |
И, да, я могу просто сделать это:
DELETE FROM user_address WHERE address_id = 1;
INSERT INTO user_address VALUES (1, 3);
Но что, если у меня будет 1 миллион строк, и пользователь захочет удалить только одну строку.
В этом случае я удалю все 1 миллион и вставлю 999.999.999 только для ОДНОЙ строки.
Итак, как я могу вставить только новые строки и удалить те, которые не были вставлены для меня?
P.S. Я использую PostgreSQL.
ОБНОВИТЬ
Надеюсь, что эти скриншоты объяснят мою проблему.
Вот моя соединительная таблица:
Я пытаюсь обновить пользователей для адреса - изменить user_id в соединении:
UPDATE user_address SET user_id = 100 WHERE address_id = 25 RETURNING user_id
Но если я использую этот запрос, он изменит оба user_id на 100, когда я хочу иметь только одну строку с user_id = 100 
Итак, вопрос в том, как избежать дубликатов при обновлении таблицы соединений.
@Ancoron Пожалуйста, проверьте мое редактирование выше, надеюсь, это объяснит мою проблему.
@Ancoron Вот хороший пример решения моей проблемы. - связь, но это MySQL. Мне нужен PostgreSQL.
Ответы 2
Во-первых, чтобы избежать и предотвратить дублирование в вашем «соединительный стол», вам нужно сделать строки уникальными, добавив ограничение:
ALTER TABLE user_address ADD CONSTRAINT uq_user_addr UNIQUE(user_id, address_id)
-- OR if there is no primary key in your user_address table
ALTER TABLE user_address ADD PRIMARY KEY (user_id, address_id)
Это гарантирует, что у вас нет дубликатов в указанной таблице. Чтобы удалить одну строку, вам нужно будет указать значения обоих столбцов. Если вы хотите удалить все строки с определенным user_id, укажите user_id; Если вы хотите удалить все строки с address_id, укажите address_id.
Вы можете сделать то же самое, что и для решения MySQL, которое вы нашли, только синтаксис будет другим.
Для INSERT вы должны добавить UNIQUE INDEX:
CREATE UNIQUE INDEX idx_address_users ON user_address (address_id, user_id);
... и затем вы можете использовать предложение ON CONFLICT (представленное в PostgreSQL 9.5), чтобы игнорировать повторяющиеся строки, например:
INSERT INTO user_address (user_id, address_id)
VALUES (100, 25), (101, 25), (102, 25)
ON CONFLICT (idx_address_users) DO NOTHING -- ignore duplicate new rows
RETURNING user_id;
DELETE FROM user_address WHERE address_id = 25 AND user_id NOT IN (100, 101, 102);
...или со стороны пользователя:
INSERT INTO user_address (user_id, address_id)
VALUES (100, 25), (100, 26), (100, 27)
ON CONFLICT (idx_address_users) DO NOTHING -- ignore duplicate new rows
RETURNING address_id;
DELETE FROM user_address WHERE user_id = 100 AND address_id NOT IN (25, 26, 27);
Однако оба этих подхода требуют, чтобы ваше приложение выполняло полный цикл загрузки-изменения-сохранения, поскольку здесь вы имеете дело с полными коллекциями.
Кроме того, дополнительный первичный ключ практически бесполезен.
Пожалуйста, просмотрите данные вашего примера, так как они не соответствуют заявлениям. Также неясно, чего вы на самом деле хотите и почему это необходимо для «удалить все 1 миллион (= 1M) и вставить 999.999.999 (= 999M ???) только для ОДНОЙ строки», не могли бы вы немного подробнее объяснить свой вариант использования?