Is it possible to reference one column as multiple foreign keys?

Question

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

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?

No, this isn't possible. Your foreign key has to point to one table, it cannot point to table1, table2 or table3 with a foreign key. You could check in a before insert and before update trigger if it exists in one of the tables — Michiel van Vaardegem, Mar 21 '13 at 12:20
Michael, surprisingly it is possible in some DBMSs to define N>1 foreign keys on the same field(s) and point them to different tables. I've just verified that in MySQL 5.5, Oracle 11g and MS SQL 2012. I think using that feature is almost always a bad idea, but it isn't impossible (and can be useful in some cases where the multiple tables being referenced as parent are all the same logical entity). See http://stackoverflow.com/questions/19057188/one-field-with-two-references-in-mysql/19057571#19057571 — Chris Johnson, Sep 30 '13 at 12:47

Marlin Pierce · Accepted Answer · 2019-02-25T02:18:02.267

45

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.

edited Feb 25 '19 at 02:18

answered Mar 21 '13 at 13:59

Marlin Pierce

9,931
4
30
52

Also note that the UML and more standard way of diagraming this table organization is to have a dashed box as the parent of the possible tables, have an "arrow" with an empty triangle as the arrow head point from the possible tables to the dashed box and have a normal arrow from the PDF table to the dashed box. – Marlin Pierce Mar 21 '13 at 14:01
OK, now it's looks like: `CREATE TABLE `pdf_created` ( `id` INT(10) UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT, `pdf_id` INT(10) UNSIGNED NOT NULL, `item_type` INT(3) UNSIGNED NOT NULL, `item_id` INT(10) UNSIGNED NOT NULL, `quantity` INT(5) UNSIGNED NOT NULL, PRIMARY KEY(`id`), FOREIGN KEY (`pdf_id`) REFERENCES `pdf` (`id`), FOREIGN KEY (`item_type`) REFERENCES `item` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;` – insict Mar 21 '13 at 14:02
I'm not sure you want or need "FOREIGN KEY (pdf_id) REFERENCES pdf (id)", but otherwise it looks good. – Marlin Pierce Mar 21 '13 at 14:20

score 14 · Answer 2 · edited May 23 '17 at 12:18

14

The problem you met is called Polymorphic Associations

Please refer this question: MySQL - Conditional Foreign Key Constraints

edited May 23 '17 at 12:18

Community

1
1

answered Mar 11 '14 at 08:12

Browny Lin

2,427
3
28
32

score 3 · Answer 3 · answered Mar 21 '13 at 12:22

3

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

answered Mar 21 '13 at 12:22

Dan Bracuk

20,699
4
26
43

score 1 · Answer 4 · answered Mar 21 '13 at 12:30

1

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.

answered Mar 21 '13 at 12:30

Marlin Pierce

9,931
4
30
52

I thought about it but it will mean that there are always two columns (Product, Header, Service) will be empty. – insict Mar 21 '13 at 12:34
No. One foreign key to Product will have the value of the primary key in the Product table. One foreign key field referencing Header will have the value of the primary key in the Header table. One foreign key field referencing Service will have the value of the primary key field in Service. Consider if the PDF uses the record with primary key 33 in Product, 57 in Header, and 82 in Service. You cannot store these three primary key values in one field. – Marlin Pierce Mar 21 '13 at 12:39
ok, but this table have `Quantity` column, so this items can not be placed in one row – insict Mar 21 '13 at 12:51
The PDF table should have columns FK_ITEM_TYPE, FK_PRODUCT_ID, FK_HEADER_ID, FK_SERVICE_ID and QUANTITY. If you have these five fields on the table why can't "this items can not be placed in one row"? – Marlin Pierce Mar 21 '13 at 13:07
Think for a moment: if you have a table with these columns, the value of column'quantity' will refer to what? header, product, or service? – insict Mar 21 '13 at 13:21
Typically it would just be the value of the quantity and not refer to any table. So if "PDF have 3 Car Wheel Product, and 1 Car Template Header above" and the Primary key of the Car Wheel Product in the product table is 33, and the PDF header information is stored in record 57 of the Header table, and the record for the service is in record 82, then there is a record in the PDF table with FK_PRODUCT_ID = 33, FK_HEADER_ID = 57, FK_SERVICE_ID = 83, QUANTITY = 3. I don't understand the problem, and think you are not communicating the problem clearly. – Marlin Pierce Mar 21 '13 at 13:32
Did you mean to ask that you have one column which refers to one of multiple tables? Are you thinking that you store the table name in FK_ITEM_TYPE and the primary key in FK_ITEM_ID? – Marlin Pierce Mar 21 '13 at 13:40
Ah, that's a totally different question. That will require a new answer. (The way you asked the question there was nothing to indicate this was what you wanted.) – Marlin Pierce Mar 21 '13 at 13:55

score 0 · Answer 5 · answered May 28 '13 at 10:48

ya its possible foreign key constraint name should be different like this and primary key and foreign key table column should have same data type like this..

CREATE TABLE `neo_address_t` (
  `address_id` varchar(8) NOT NULL,
  `address_line_1` varchar(45) NOT NULL,
  `address_line_2` varchar(45) NOT NULL,
  `address_line_3` varchar(45) NOT NULL,
  `address_city` varchar(45) NOT NULL,
  `address_zipcode` varchar(45) NOT NULL,
  `address_state` varchar(45) NOT NULL DEFAULT 'Karnataka',
  `address_country` varchar(45) NOT NULL DEFAULT 'INDIA',
  `created_by` varchar(8) DEFAULT NULL,
  `created_on` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_modified_by` varchar(8) DEFAULT NULL,
  `last_modified_date` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `Refer_ID` int(11) DEFAULT NULL,
  `a_id` varchar(255) DEFAULT NULL,
  `referenceid` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`address_id`),
  KEY `hospital_ID_FK_idx` (`Refer_ID`),
  CONSTRAINT `Patient_ID_FK` FOREIGN KEY (`Refer_ID`) REFERENCES `neo_patient_t` (`patient_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `hospital_ID_FK` FOREIGN KEY (`Refer_ID`) REFERENCES `neo_hospital_t` (`hospital_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `staff_ID_FK` FOREIGN KEY (`Refer_ID`) REFERENCES `neo_staff_t` (`staff_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
)     
ENGINE=InnoDB DEFAULT CHARSET=utf8$$

score 0 · Answer 6 · answered Apr 08 '16 at 06:29

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.

René Link · Answer 7 · 2018-08-27T09:43:34.123

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).

score 0 · Answer 8 · answered Nov 03 '21 at 07:40

Yes It is possible, You can use one column as multple table's foreign key, but it is not good practice as well, Because you would never as depned as this much where you needed to use this type of method but for lerning perpose you can use this type of SQL Queries do this job ;)

ALTER TABLE demo4 ADD CONSTRAINT rel1 FOREIGN KEY (two) REFERENCES demo1(id) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE demo4 ADD CONSTRAINT rel2 FOREIGN KEY (two) REFERENCES demo2(id) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE demo4 ADD CONSTRAINT rel3 FOREIGN KEY (two) REFERENCES demo3(id) ON DELETE RESTRICT ON UPDATE RESTRICT;

Is it possible to reference one column as multiple foreign keys?

8 Answers8

Linked