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

Ну вот моя проблема, у меня три таблицы; регионов, стран, штатов. Страны могут находиться внутри регионов, штаты могут находиться внутри регионов. Регионы являются вершиной пищевой цепи.

Теперь я добавляю таблицу popular_areas с двумя столбцами; region_id и popular_place_id. Можно ли сделать public_place_id внешним ключом для состояний стран или стран. Вероятно, мне придется добавить столбец popular_place_type, чтобы определить, указывает ли id стране или государству в любом случае.

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

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 в качестве надёжного. Как упоминает Аби, второй альтернативой является то, что ваши популярные места ссылаются на таблицу, такую ​​как 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 представляет собой столбец с двумя значениями: это либо государство, либо страна. Вы не сохранили бы age человека и его номер phone_number в одном столбце, и по той же причине вы не должны хранить как state_id и country_id в одном столбце. Тот факт, что эти два атрибута имеют совместимые типы данных, является совпадением; они все еще означают разные логические сущности.

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


Re comment от @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) ... 

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

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

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

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

Вот поправка к «надёжному» подходу Билла Карвина с использованием сложного ключа ( 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, вы можете определить отложенные межсетевые ограничения, которые позволят вам достичь того же, но они неуклюжи, связаны с транзакцией, и такая СУБД еще не вышла на рынок.

Давайте будем гением компьютера.