Can a Single Column Serve as Multiple Foreign Keys in SQL?

Referencing One Column as Multiple Foreign Keys

In database design, there may be situations where you desire to establish relationships between different tables using a single column as foreign keys to multiple target tables. However, this specific scenario, as presented in the SQL code snippet below, is not feasible:

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

The reason for this is that enforcing foreign key constraints that require a single column to match primary keys in multiple tables is impossible in relational database theory. While you can create columns in the pdf_created table that reference corresponding columns in the product, service, header, and item tables, establishing foreign key constraints for each of these references simultaneously is not allowed.

However, it is possible to utilize the referenced column values for joins without defining explicit foreign key constraints. A foreign key is essentially a column that contains the value of an associated table's primary key. This allows you to join records between tables based on their shared foreign key values. In the given scenario, you could have the item_id column in the pdf_created table reference the primary keys in the product, service, header, and item tables. By doing so, you can join the pdf_created table with any of these target tables using the item_id column as the join condition.

The above is the detailed content of Can a Single Column Serve as Multiple Foreign Keys in SQL?. For more information, please follow other related articles on the PHP Chinese website!