MYSQL — один столбец ссылается на несколько таблиц

Очень поздний ответ, но для тех, кто интересуется и гуглит.

ДА это можно сделать, но это НЕ хорошая практика, и хотя это довольно просто, это, вероятно, взорвется вам в лицо, если вы не очень понимаете, что делаете. Не рекомендуется.

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

Конечно, вам понадобятся два поля для связи: поле внешнего ключа и имя таблицы, с которой оно связано. Назовем их foreignId и linkedTable

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

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

Теперь предположим, что linkedTable — это перечисление с возможными значениями pets, hobbies, sports и professions, которые являются именами четырех таблиц с различной структурой. Предположим, что id является pkey во всех четырех из них.

Вы присоединяетесь, например, следующим образом:

SELECT * FROM users 
    LEFT JOIN  pets        ON linkedTable = 'pets'        AND foreignId = pets.id
    LEFT JOIN  hobbies     ON linkedTable = 'hobbies'     AND foreignId = hobbies.id
    LEFT JOIN  sports      ON linkedTable = 'sports'      AND foreignId = sports.id
    LEFT JOIN  professions ON linkedTable = 'professions' AND foreignId = professions.id

Это просто, чтобы дать основную шутку. Поскольку вам, вероятно, нужна ссылка только в редких случаях, вы, скорее всего, будете выполнять поиск на своем языке программирования, таком как PHP, когда вы перебираете пользователей (без объединения).

Хотите попробовать? Вы можете сами попробовать создать эту тестовую базу данных (убедитесь, что вы используете тестовую базу данных):

CREATE TABLE IF NOT EXISTS `users` (
    `id` INT NOT NULL AUTO_INCREMENT , 
    `name` VARCHAR(100) NOT NULL , 
    `linkedTable` ENUM('pets','hobbies','sports','professions') NULL DEFAULT NULL , 
    `foreignId` INT NULL DEFAULT NULL , 
  PRIMARY KEY (`id`), INDEX (`linkedTable`)
) ;

CREATE TABLE  IF NOT EXISTS `pets` ( 
    `id` INT NOT NULL AUTO_INCREMENT , 
    `animalTypeId` INT NOT NULL , 
    `name` VARCHAR(100) NOT NULL , 
    `colorId` INT NOT NULL , 
  PRIMARY KEY (`id`), INDEX (`animalTypeId`), INDEX (`colorId`)
) ;

CREATE TABLE  IF NOT EXISTS `hobbies` ( 
    `id` INT NOT NULL AUTO_INCREMENT , 
    `hobbyTypeId` INT NOT NULL , 
    `hoursPerWeekSpend` INT NOT NULL , 
    `websiteUrl` VARCHAR(300) NULL , 
  PRIMARY KEY (`id`), INDEX (`hobbyTypeId`)
) ;

CREATE TABLE  IF NOT EXISTS `sports` ( 
    `id` INT NOT NULL AUTO_INCREMENT , 
    `sportTypeId` INT NOT NULL , 
    `hoursPerWeekSpend` INT NOT NULL , 
    `nameClub` VARCHAR(100) NULL , 
    `professional` TINYINT NOT NULL DEFAULT 0, 
  PRIMARY KEY (`id`), INDEX (`sportTypeId`)
) ;

CREATE TABLE  IF NOT EXISTS `professions` ( 
    `id` INT NOT NULL AUTO_INCREMENT , 
    `professionId` INT NOT NULL , 
    `hoursPerWeek` INT NOT NULL , 
    `nameCompany` VARCHAR(100) NULL , 
    `jobDescription` VARCHAR(400) NULL, 
  PRIMARY KEY (`id`), INDEX (`professionId`)
) ;


INSERT INTO `users` (`id`, `name`, `linkedTable`, `foreignId`) 
   VALUES 
   (NULL, 'Hank', 'pets', '1'), 
   (NULL, 'Peter', 'hobbies', '2'), 
   (NULL, 'Muhammed', 'professions', '1'), 
   (NULL, 'Clarice', NULL, NULL), 
   (NULL, 'Miryam', 'professions', '2'), 
   (NULL, 'Ming-Lee', 'hobbies', '1'), 
   (NULL, 'Drakan', NULL, NULL), 
   (NULL, 'Gertrude', 'sports', '2'), 
   (NULL, 'Mbase', NULL, NULL);


INSERT INTO `pets` (`id`, `animalTypeId`, `name`, `colorId`) 
VALUES (NULL, '1', 'Mimi', '3'), (NULL, '2', 'Tiger', '8');

INSERT INTO `hobbies` (`id`, `hobbyTypeId`, `hoursPerWeekSpend`, `websiteUrl`) 
VALUES (NULL, '123', '21', NULL), (NULL, '2', '1', 'http://www.freesoup.org');

INSERT INTO `sports` (`id`, `sportTypeId`, `hoursPerWeekSpend`, `nameClub`, `professional`) 
VALUES (NULL, '2', '3', 'Racket to Racket', '0'), (NULL, '12', '34', NULL, '1');

INSERT INTO `professions` (`id`, `professionId`, `hoursPerWeek`, `nameCompany`, `jobDescription`) 
VALUES (NULL, '275', '40', 'Ben & Jerry\'s', 'Ice cream designer'), (NULL, '21', '24', 'City of Dublin', 'Garbage collector');

Затем выполните первый запрос.

Забавная заметка для обсуждения: как бы вы проиндексировали это?

Если вы имеете в виду "может ли столбец в одной таблице использоваться в качестве внешнего ключа в нескольких таблицах", то ответ будет ДА. В этом весь смысл реляционной базы данных.

Да, вы можете сделать это так. вот пример того, как это сделать:

Вот таблица, в которой есть столбец (CountryID), на который будут ссылаться несколько таблиц:

CREATE TABLE DLAccountingSystem.tblCountry
(
    CountryID       INT             AUTO_INCREMENT NOT NULL PRIMARY KEY,
    CountryName     VARCHAR(128)    NOT NULL,
    LastEditUser    VARCHAR(128)    NOT NULL,
    LastEditDate    DATETIME        NOT NULL
) ENGINE=INNODB;

Вот таблицы, которые будут ссылаться на столбец (CountryID):

CREATE TABLE DLAccountingSystem.tblCity
(
    CityID          INT             AUTO_INCREMENT NOT NULL PRIMARY KEY,
    CountryID       INT             NOT NULL,
    CityName        VARCHAR(128)    NOT NULL,
    LastEditUser    VARCHAR(128)    NOT NULL,
    LastEditDate    DATETIME        NOT NULL
) ENGINE=INNODB;

CREATE TABLE DLAccountingSystem.tblProvince
(
    ProvinceID      INT             AUTO_INCREMENT NOT NULL PRIMARY KEY,
    CountryID       INT             NOT NULL,
    ProvinceName    VARCHAR(128)    NOT NULL,
    LastEditUser    VARCHAR(128)    NOT NULL,
    LastEditDate    DATETIME        NOT NULL
) ENGINE=INNODB;

Вот как вы создаете ссылку на столбец:

 ALTER TABLE DLAccountingSystem.tblCity
 ADD CONSTRAINT fk_tblcitycountryid FOREIGN KEY CountryID (CountryID)
 REFERENCES DLAccountingSystem.tblCountry (CountryID)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION

 ALTER TABLE DLAccountingSystem.tblProvince
 ADD CONSTRAINT fk_tblprovincecountryid FOREIGN KEY CountryID (CountryID)
 REFERENCES DLAccountingSystem.tblCountry (CountryID)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION

вот таблица, в которой есть столбец, который ссылается на разные столбцы из нескольких таблиц (CountryID, ProvinceID, CityID) (лично я не рекомендую такой способ структурирования таблицы. Просто мое мнение без обид;))

CREATE TABLE DLAccountingSystem.tblPersons
(
    PersonID       INT          AUTO_INCREMENT NOT NULL PRIMARY KEY,
    PlaceID        INT          NOT NULL,
    PlaceTypeID    INT          NOT NULL, -- this property refers to what table are you referencing.
 //Other properties here.....
) ENGINE=INNODB;

у вас также должна быть таблица поиска, которая будет содержать PlaceType:

CREATE TABLE DLAccountingSystem.tblPlaceType
(
    PlaceTypeID       INT          AUTO_INCREMENT NOT NULL PRIMARY KEY,
    PlaceTypeName        INT          NOT NULL
 //Other properties here.....
) ENGINE=INNODB;

вот как вы его получаете:

SELECT p1.PersonID,
       tcity.CityName,
       tprov.ProvinceName,
       tcoun.CountryName
FROM DLAccountingSystem.tblPersons p1 
LEFT JOIN (SELECT p2.PersonID, p2.PlaceTypeID, c.CityName FROM DLAccountingSystem.tblPersons p2 INNER JOIN DLAccountingSystem.tblCity c ON p2.ObjectID = c.CityID WHERE PlaceTypeID = @CityTypeID) tcity ON p1.PersonID = tcity.PersonID
LEFT JOIN (SELECT p2.PersonID, p2.PlaceTypeID, c.ProvinceName FROM DLAccountingSystem.tblPersons p2 INNER JOIN DLAccountingSystem.tblProvince c ON p2.ObjectID = c.ProvinceID WHERE PlaceTypeID = @ProvinceTypeID) tprov ON p1.PersonID = tprov.PersonID
LEFT JOIN (SELECT p2.PersonID, p2.PlaceTypeID, c.CountryName FROM DLAccountingSystem.tblPersons p2 INNER JOIN DLAccountingSystem.tblCountry c ON p2.ObjectID = c.CountryID WHERE PlaceTypeID = @CountryTypeID) tcoun ON p1.PersonID = tcoun.PersonID

вы можете выбрать из других таблиц, таких как

Один и тот же столбец или набор столбцов могут выступать в качестве родительской и/или дочерней конечной точки внешнего ключа или внешних ключей.