Блог

У меня есть таблица отношений «многие ко многим» в виде:

Таблица relationship :

 relationship_id,
first_user REFERENCES users(user_id),
second_user REFERENCES users(user_id),
  

Таблица users :

 user_id,
other user information
  

Чтобы прочитать друзей данного пользователя (которые могут быть в first_user или second_user ), мне нужно объединить две таблицы ( relationships и users ), но два столбца таблицы relationships являются FK

Вопрос 1: Как я могу ОБЪЕДИНИТЬ две таблицы, чтобы использовать обе FK?

У меня есть таблица для ожидающих запросов, которая похожа на таблицу relationships . После утверждения запроса он будет удален из requests и вставлен в relationships .

Вопрос 2: Как я могу ОБЪЕДИНИТЬ три таблицы, чтобы получить соединение и ожидающие запросы в одном запросе.

Вам нужно будет присвоить экземплярам таблицы «users» псевдонимы, чтобы вы могли ссылаться на них отдельно:

 SELECT u1.*, u2.* FROM relationship r
  JOIN users u1 ON (u1.user_id = r.first_user)
  JOIN users u2 ON (u2.user_id = r.second_user)
  

Чтобы выбрать оба из запросов и отношений, вы можете использовать ОБЪЕДИНЕНИЕ:

 SELECT u1.*, u2.* FROM relationship r
  JOIN users u1 ON (u1.user_id = r.first_user)
  JOIN users u2 ON (u2.user_id = r.second_user)
UNION
SELECT u1.*, u2.* FROM requests r
  JOIN users u1 ON (u1.user_id = r.first_user)
  JOIN users u2 ON (u2.user_id = r.second_user)
  

Чтобы использовать оба внешних ключа, вам нужно два соединения (по одному для каждого):

 select * 
  from relationship r 
 inner join users u1 on (u1.user_id = r.first_user)
 inner join users u2 on (u2.user_id = r.second_user)
  

Таблица {relationship_id, first_user, second_user} содержит повторяющуюся группу.

Просто удалите один из них (который делает relationship_id неуникальным) и добавьте суррогатный ключ (или добавьте userid к первичному ключу).

 CREATE TABLE relationships
( id INTEGER NOT NULL PRIMARY KEY
, relationship_id INTEGER NOT NULL
, user_id INTEGER NOT NULL REFERENCES users(user_id)
);
  

Было бы хорошим домашним заданием попытаться сгенерировать ограничения для этой конструкции.

Другой способ (для бинарных отношений, таких как браки) — поместить ссылку «значимый другой» в таблицу users:

  CREATE TABLE lusers
 ( luser_id INTEGER NOT NULL PRIMARY KEY
 , magnificent_other INTEGER REFERENCES lusers (luser_id)
 ...
 );
  

В качестве побочного эффекта это также сделает невозможным двоеженство.
Опять же: хорошее домашнее задание по реализации ограничений.