是否可以将一列引用为多个外键? [英] Is it possible to reference one column as multiple foreign keys?

问题描述

我有几个表格，我想将 PDF 表格中的一列引用到多个其他表格.

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

例如，如果 PDF 表 select 输出如下所示:

for example if PDF table select output looks like this:

ITEM_TYPE  ITEM_ID  QUANTITY

1          23       3
2          12       1

它告诉我:

PDF 包含 3 个汽车车轮产品和 1 个汽车模板标题；

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

我写了 SQL 代码，但不能正常工作:

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;

可以创建吗?

推荐答案

没有

也就是说，您不能以这种方式创建外键约束.但是，您可以使用没有外键约束的外键.

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.

然而，外键约束告诉数据库强制执行规则，对于表中的每个外键值，被引用的表都有一个记录作为它的主键.强制要求 PDF 表中的每个外键都有一个主键 在所有四个表中 对您不起作用.所以继续使用该字段来引用其他记录，但不要创建任何外键约束.

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.

这篇关于是否可以将一列引用为多个外键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！