Is it possible to reference one column as multiple

Question

I have few tables, and I want to reference one column from PDF table to multiple other tables.

for example if PDF table select output looks like this:

ITEM_TYPE  ITEM_ID  QUANTITY

1          23       3
2          12       1

it tells me:

PDF have 3 Car Wheel Product, and 1 Car Template Header above;

I wrote SQL code, but does not work properly:

CREATE TABLE `pdf_created` (
    `id` INT(10) UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
    `pdf_id` INT(10) NOT NULL,
    `item_type` INT(3) UNSIGNED NOT NULL,
    `item_id` INT(10) UNSIGNED NOT NULL,
    `quantity` INT(3) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `FK_pdf_id` (`pdf_id`),
    CONSTRAINT `FK_pdf_id` FOREIGN KEY (`pdf_id`) REFERENCES `pdf` (`id`),
    KEY `FK_item_type` (`item_type`),
    CONSTRAINT `FK_item_type` FOREIGN KEY (`item_type`) REFERENCES `item` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    KEY `FK_item_id` (`item_id`),
    CONSTRAINT `FK_item_id` FOREIGN KEY (`item_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK_item_id` FOREIGN KEY (`item_id`) REFERENCES `service` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK_item_id` FOREIGN KEY (`item_id`) REFERENCES `header` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `header` (
    `id` INT(10) UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
    `title` VARCHAR(255),
    `desc` VARCHAR(65535),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `service` (
    `id` INT(10) UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
    `desc` VARCHAR(65535) NOT NULL,
    `price` DECIMAL(5,2) NOT NULL,
    `active` INT(1) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `product` (
    `id` INT(10) UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
    `category_id` INT(3) UNSIGNED NOT NULL,
    `symbol` VARCHAR(255),
    `desc` VARCHAR(65535), 
    `price` DECIMAL(5,2) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

it is possible to create it?

Answer 1

No.

That is, you cannot create a foreign key constraint this way. You can however, use a foreign key without a foreign key constraint.

All a foreign key is, is the value of another table's (or another record in the same table) primary key, which can be used in joins. In fact, you could reference fields other than the primary key, if all you need is to use the value for joins.

However, a foreign key constraint tells the database to enforce the rule that for every foreign key value in a table, the referenced table has a record with that as it's primary key. Enforcing that every foreign key in the PDF table had a primary key IN ALL FOUR TABLES won't work for you. So go ahead and use the field to reference other records, but simply do not create any foreign key constraint.

Answer 2

Yes, it's possible. Even if it seems a bit strange.

First I would like to show a screenshot of my MySQL Workbench to testify that it works.

...and for those that say "Maybe you forgot to apply the changes"... Here is the screenshot of the schema browser:

and finally the exported dump with some example data:

-- MySQL dump 10.13  Distrib 5.7.12, for Win64 (x86_64)
--
-- Host: localhost    Database: multiple_foreign_keys
-- ------------------------------------------------------
-- Server version   5.7.17-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `header`
--

DROP TABLE IF EXISTS `header`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `header` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `desc` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `header`
--

LOCK TABLES `header` WRITE;
/*!40000 ALTER TABLE `header` DISABLE KEYS */;
INSERT INTO `header` VALUES (42,'Header','Test Header');
/*!40000 ALTER TABLE `header` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pdf_created`
--

DROP TABLE IF EXISTS `pdf_created`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pdf_created` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pdf_id` int(10) NOT NULL,
  `item_type` int(3) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `quantity` int(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `fk_item_to_product_idx` (`item_id`),
  CONSTRAINT `fk_item_to_header` FOREIGN KEY (`item_id`) REFERENCES `header` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_item_to_product` FOREIGN KEY (`item_id`) REFERENCES `product` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_item_to_service` FOREIGN KEY (`item_id`) REFERENCES `service` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pdf_created`
--

LOCK TABLES `pdf_created` WRITE;
/*!40000 ALTER TABLE `pdf_created` DISABLE KEYS */;
INSERT INTO `pdf_created` VALUES (1,2,5,42,1);
/*!40000 ALTER TABLE `pdf_created` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `product`
--

DROP TABLE IF EXISTS `product`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `product` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(3) unsigned NOT NULL,
  `symbol` varchar(255) DEFAULT NULL,
  `desc` varchar(255) DEFAULT NULL,
  `price` decimal(5,2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `product`
--

LOCK TABLES `product` WRITE;
/*!40000 ALTER TABLE `product` DISABLE KEYS */;
INSERT INTO `product` VALUES (42,13,'product','desc',10.00);
/*!40000 ALTER TABLE `product` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `service`
--

DROP TABLE IF EXISTS `service`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `service` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `desc` varchar(255) NOT NULL,
  `price` decimal(5,2) NOT NULL,
  `active` int(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `service`
--

LOCK TABLES `service` WRITE;
/*!40000 ALTER TABLE `service` DISABLE KEYS */;
INSERT INTO `service` VALUES (42,'some service',5.00,1);
/*!40000 ALTER TABLE `service` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-08-27 10:31:41

Use 'Data Import/Restore' in MySQL Workbench if you want to test it instead of an SQL script editor.

BUT you should not use auto-increment for the product, header and service tables that are referenced by pdf_created.item_id, because it would be a lucky coincidence if all three will get the same id. You should assign the id (the item's id).

Answer 3

The problem you met is called Polymorphic Associations

Please refer this question: MySQL - Conditional Foreign Key Constraints

Answer 4

No, one foreign key field is meant to reference one table.

If you did have the FK constraints as you describe, a item_id field would reference the same primary key value in all three tables. It would be very likely that the desired primary key in the three different tables would have different primary keys.

What you want is for one record (row) to reference records in tables Product, Header, and Service. The way to do that is to use three different fields, one for each foreign key.

I also notice that the Item table has the three foreign keys needed. You could have the PDF table have one field which references Item, and the record in Item references the three other tables.

Answer 5

It should be possible. One potential problem is that your three foreign key constraints have the same name.

Answer 6

Theoretically you can not enforce multiple foreign key on single column. Alternatively you can enforce this using procedures where you validate the input which exists in multiple table and do the needful operation. Be careful that all the operation on that particular table should be done by the procedures which validates the required condition else it would leads in violation of integrity.