Можно ли использовать внешний ключ MySQL для одной из двух возможных таблиц?

Ответ 1

То, что вы описываете, называется Полиморфными ассоциациями. То есть столбец "внешний ключ" содержит значение id, которое должно существовать в одном из набора целевых таблиц. Как правило, целевые таблицы каким-то образом связаны, например, являются экземплярами некоторого общего суперкласса данных. Вам также понадобится другой столбец рядом с столбцом внешнего ключа, так что в каждой строке вы можете указать, на какую целевую таблицу ссылаются.

CREATE TABLE popular_places (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  place_type VARCHAR(10) -- either 'states' or 'countries'
  -- foreign key is not possible
);

Невозможно моделировать полиморфные ассоциации с использованием ограничений SQL. Ограничение внешнего ключа всегда ссылается на целевую таблицу одна.

Полиморфные ассоциации поддерживаются такими фреймворками, как Rails и Hibernate. Но они явно говорят, что вы должны отключить SQL-ограничения для использования этой функции. Вместо этого приложение или структура должны выполнять эквивалентную работу, чтобы гарантировать, что ссылка выполнена. То есть значение во внешнем ключе присутствует в одной из возможных целевых таблиц.

Полиморфные ассоциации слабы в отношении обеспечения согласованности базы данных. Целостность данных зависит от всех клиентов, обращающихся к базе данных с той же логикой ссылочной целостности, что и принудительное исполнение, должно быть без ошибок.

Вот некоторые альтернативные решения, которые используют преимущества ссылочной целостности, привязанные к базам данных:

Создайте одну дополнительную таблицу для каждой цели. Например popular_states и popular_countries, которые ссылаются на states и countries соответственно. Каждая из этих "популярных" таблиц также ссылается на профиль пользователя.

CREATE TABLE popular_states (
  state_id INT NOT NULL,
  user_id  INT NOT NULL,
  PRIMARY KEY(state_id, user_id),
  FOREIGN KEY (state_id) REFERENCES states(state_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

CREATE TABLE popular_countries (
  country_id INT NOT NULL,
  user_id    INT NOT NULL,
  PRIMARY KEY(country_id, user_id),
  FOREIGN KEY (country_id) REFERENCES countries(country_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

Это означает, что для получения всех популярных любимых мест пользователей вам нужно запросить обе эти таблицы. Но это означает, что вы можете полагаться на базу данных, чтобы обеспечить согласованность.

Создайте таблицу places как supertable. Как упоминает Abie, второй альтернативой является то, что ваши популярные места ссылаются на таблицу типа places, которая является родительской для обоих states и countries. То есть, оба государства и страны также имеют внешний ключ для places (вы даже можете сделать этот внешний ключ также основным ключом states и countries).

CREATE TABLE popular_areas (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  PRIMARY KEY (user_id, place_id),
  FOREIGN KEY (place_id) REFERENCES places(place_id)
);

CREATE TABLE states (
  state_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (state_id) REFERENCES places(place_id)
);

CREATE TABLE countries (
  country_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

Использовать два столбца. Вместо одного столбца, который может ссылаться на одну из двух целевых таблиц, используйте два столбца. Эти два столбца могут быть NULL; на самом деле только один из них должен быть не NULL.

CREATE TABLE popular_areas (
  place_id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  state_id INT,
  country_id INT,
  CONSTRAINT UNIQUE (user_id, state_id, country_id), -- UNIQUE permits NULLs
  CONSTRAINT CHECK (state_id IS NOT NULL OR country_id IS NOT NULL),
  FOREIGN KEY (state_id) REFERENCES places(place_id),
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

В терминах реляционной теории Полиморфные ассоциации нарушают Первая нормальная форма, потому что popular_place_id представляет собой столбец с двумя значениями: it либо государство, либо страна. Вы не сохранили бы человека age и их phone_number в одном столбце, и по той же причине вы не должны хранить как state_id, так и country_id в одном столбце. Тот факт, что эти два атрибута имеют совместимые типы данных, является совпадением; они по-прежнему обозначают разные логические объекты.

Полиморфные ассоциации также нарушают Третью нормальную форму, поскольку значение столбца зависит от дополнительного столбца, который называет таблицу, к которой относится внешний ключ относится. В третьей нормальной форме атрибут в таблице должен зависеть только от первичного ключа этой таблицы.

Re comment from @SavasVedova:

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

CREATE TABLE Products (
  product_id INT PRIMARY KEY
);

CREATE TABLE FiltersType1 (
  filter_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE FiltersType2 (
  filter_id INT  PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

...and other filter tables...

Присоединение продуктов к определенному типу фильтра легко, если вы знаете, к какому типу вы хотите присоединиться:

SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)

Если вы хотите, чтобы тип фильтра был динамическим, вы должны написать код приложения для построения SQL-запроса. SQL требует, чтобы таблица была указана и исправлена ​​во время написания запроса. Вы не можете сделать динамически объединенную таблицу, основанную на значениях, найденных в отдельных строках Products.

Единственный другой вариант - присоединиться ко всем таблицам фильтров с использованием внешних соединений. Те, у кого нет соответствующего product_id, будут возвращены только как одна строка с нулями. Но вам все равно придется жестко скопировать все объединенные таблицы, и если вы добавите новые таблицы фильтров, вам необходимо обновить свой код.

SELECT * FROM Products
LEFT OUTER JOIN FiltersType1 USING (product_id)
LEFT OUTER JOIN FiltersType2 USING (product_id)
LEFT OUTER JOIN FiltersType3 USING (product_id)
...

Еще один способ присоединиться ко всем таблицам фильтров - сделать это серийно:

SELECT * FROM Product
INNER JOIN FiltersType1 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType3 USING (product_id)
...

Но этот формат по-прежнему требует, чтобы вы записывали ссылки на все таблицы. Там не обойти это.

Ответ 2

Это не самое элегантное решение в мире, но вы можете использовать конкретное наследование таблицы, чтобы сделать эту работу.

Концептуально вы предлагаете понятие класса "вещей, которые могут быть популярными областями", из которых наследуются ваши три типа мест. Вы можете представить это как таблицу, называемую, например, places, где каждая строка имеет взаимно-однозначное отношение со строкой в ​​regions, countries или states. (Атрибуты, которые разделены между регионами, странами или государствами, если таковые имеются, могут быть перенесены в эту таблицу мест.) Ваш popular_place_id будет тогда ссылкой внешнего ключа на строку в таблице мест, которая затем приведет вас к региона, страны или штата.

Решение, которое вы предлагаете со вторым столбцом для описания типа ассоциации, заключается в том, как Rails обрабатывает полиморфные ассоциации, но я не поклонник этого в целом. Билл прекрасно объясняет, почему полиморфные ассоциации не являются вашими друзьями.

Ответ 3

Вот исправление к "надёжному" подходу Билла Карвина, используя составной ключ ( place_type, place_id ) для устранения предполагаемых нарушений нормальной формы:

CREATE TABLE places (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) NOT NULL
     CHECK ( place_type = 'state', 'country' ),
  UNIQUE ( place_type, place_id )
);

CREATE TABLE states (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) DEFAULT 'state' NOT NULL
     CHECK ( place_type = 'state' ),
  FOREIGN KEY ( place_type, place_id ) 
     REFERENCES places ( place_type, place_id )
  -- attributes specific to states go here
);

CREATE TABLE countries (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) DEFAULT 'country' NOT NULL
     CHECK ( place_type = 'country' ),
  FOREIGN KEY ( place_type, place_id ) 
     REFERENCES places ( place_type, place_id )
  -- attributes specific to country go here
);

CREATE TABLE popular_areas (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  UNIQUE ( user_id, place_id ),
  FOREIGN KEY ( place_type, place_id ) 
     REFERENCES places ( place_type, place_id )
);

Что этот проект не может гарантировать, что для каждой строки в places существует строка в states или countries (но не для обоих). Это ограничение внешних ключей в SQL. В полной СУБД, совместимой со стандартами SQL-92, вы можете определить отложенные ограничения между таблицами, которые позволят вам достичь того же, но они неуклюжи, связаны с транзакцией, и такая СУБД еще не вышла на рынок.

Ответ 4

Я понимаю, что эта ветка старая, но я увидела это, и решение пришло мне на ум, и я подумала, что брошу это туда.

Регионы, страны и государства - это географические местоположения, которые живут в иерархии.

Вы могли бы полностью избежать вашей проблемы, создав таблицу домена с именем geometry_location_type, которую вы бы заполнили тремя строками (Регион, Страна, Штат).

Затем, вместо трех таблиц местоположения, создайте одну таблицу географического расположения, которая имеет внешний ключographic_location_type_id (чтобы вы знали, является ли экземпляр регионом, страной или государством).

Смоделируйте иерархию, сделав для этой таблицы самоссылку так, чтобы экземпляр State содержал fKey в своем родительском экземпляре Country, который, в свою очередь, хранит fKey в своем родительском экземпляре Region. Экземпляры региона будут содержать значение NULL в этом ключе. Это ничем не отличается от того, что вы сделали бы с тремя таблицами (у вас было бы 1 - много отношений между регионом и страной, а также между страной и государством), за исключением того, что теперь все это в одной таблице.

Таблица Popular_user_Location будет таблицей разрешения области между пользователем и georgraphical_location (так многим пользователям может понравиться много мест).

Оооочень…

CREATE TABLE [geographical_location_type] (
    [geographical_location_type_id] INTEGER NOT NULL,
    [name] VARCHAR(25) NOT NULL,
    CONSTRAINT [PK_geographical_location_type] PRIMARY KEY ([geographical_location_type_id])
)

-- Add 'Region', 'Country' and 'State' instances to the above table


CREATE TABLE [geographical_location] (
   [geographical_location_id] BIGINT IDENTITY(0,1) NOT NULL,
    [name] VARCHAR(1024) NOT NULL,
    [geographical_location_type_id] INTEGER NOT NULL,
    [geographical_location_parent] BIGINT,  -- self referencing; can be null for top-level instances
    CONSTRAINT [PK_geographical_location] PRIMARY KEY ([geographical_location_id])
)

CREATE TABLE [user] (
    [user_id] BIGINT NOT NULL,
    [login_id] VARCHAR(30) NOT NULL,
    [password] VARCHAR(512) NOT NULL,
    CONSTRAINT [PK_user] PRIMARY KEY ([user_id])
)


CREATE TABLE [popular_user_location] (
    [popular_user_location_id] BIGINT NOT NULL,
    [user_id] BIGINT NOT NULL,
    [geographical_location_id] BIGINT NOT NULL,
    CONSTRAINT [PK_popular_user_location] PRIMARY KEY ([popular_user_location_id])
)

ALTER TABLE [geographical_location] ADD CONSTRAINT [geographical_location_type_geographical_location] 
    FOREIGN KEY ([geographical_location_type_id]) REFERENCES [geographical_location_type] ([geographical_location_type_id])



ALTER TABLE [geographical_location] ADD CONSTRAINT [geographical_location_geographical_location] 
    FOREIGN KEY ([geographical_location_parent]) REFERENCES [geographical_location] ([geographical_location_id])



ALTER TABLE [popular_user_location] ADD CONSTRAINT [user_popular_user_location] 
    FOREIGN KEY ([user_id]) REFERENCES [user] ([user_id])



ALTER TABLE [popular_user_location] ADD CONSTRAINT [geographical_location_popular_user_location] 
    FOREIGN KEY ([geographical_location_id]) REFERENCES [geographical_location] ([geographical_location_id])

Не был уверен, какова была целевая БД; выше MS SQL Server.