是否可以将一列引用为多个外键? [英] 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屋！