0

How to reference one field to two tables?

In my tables field location.location_id must be referenced to city.id and country.id

In table location I have location_type field, for determine which parent table must be used.

CREATE TABLE `location` (
`id` mediumint UNSIGNED NOT NULL AUTO_INCREMENT,
`location_id` mediumint UNSIGNED NOT NULL,
`location_type` enum('city','country') NOT NULL,
PRIMARY KEY (`id`) 
);

CREATE TABLE `city` (
`id` mediumint UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
PRIMARY KEY (`id`) 
);

CREATE TABLE `country` (
`id` mediumint UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
PRIMARY KEY (`id`) 
);

Is there any model for this reference, may be I need special referencing tables?

Vladimir
  • 229
  • 2
  • 16
  • Answers above absolutely not for my question. I need referencing one field in one table to two different tables, not conversely. One table with one field is child, two different tables is parents. – Vladimir Feb 16 '18 at 11:23
  • Your question is a duplicate. Your situation calls for the design in the duplicate. You don't *want* "to reference one field to two tables" via FKs, since there is no such thing, since a FK says values must appear in a certain single other place. This just reminds you of FKs. Two *types* of things can have their ids as location ids; those types are *subtypes* of location. Location is their supertype. The 2 are children under the relation "is a supertype of". Whether they would be parents under your "is referenced by a [thing reminiscent of a] FK" is irrelevant. – philipxy Feb 18 '18 at 11:01

0 Answers0