How Can Multiple Foreign Key References to a Single Column Be Handled in Database Design?

Multiple Foreign Key References to a Single Column

In database design, it can be beneficial to reference a single column in one table as foreign keys in multiple other tables. This approach enables seamless data association and consistent data retrieval across multiple entities.

Problem Statement

Consider the following database scenario:

You have a table called pdf_created with columns item_type, item_id, and quantity. Each row in this table represents an item extracted from a PDF document. The item_type column identifies the type of item (e.g., "1" for product, "2" for template header). The item_id column references the primary key of the corresponding item table (e.g., product, service, header). The goal is to reference the item_id column as a foreign key in all three item tables: product, service, and header.

However, the following SQL code fails to create the desired relationships:

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;

Reason for Failure

The problem lies in the multiple foreign key constraints on the item_id column. A foreign key constraint ensures that every value in the item_id column is present as a primary key value in at least one of the four tables (product, service, header). However, it is impossible to guarantee that item_id will always exist as a primary key in all four tables simultaneously.

Solution

While it is not possible to create multiple foreign key constraints on a single column that references different tables, you can still use the item_id column to reference the three item tables. Remove all foreign key constraints on the item_id column and rely on application code to enforce the referential integrity:

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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This allows you to maintain the necessary data relationships without introducing data integrity errors.

The above is the detailed content of How Can Multiple Foreign Key References to a Single Column Be Handled in Database Design?. For more information, please follow other related articles on the PHP Chinese website!