可以将一列作为多个外键来引用 [英] it is possible to reference one column as multiple foreign keys

问题描述

我有很少的表，我想从PDF表中引用一列到多个其他表。

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

  ITEM_TYPE ITEM_ID QUANTITY 
 
 1 23 3 
 2 12 1 
  

它告诉我：b
$ b PDF有3个车轮产品，1个车模板头以上;

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

  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 ``）参考`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`） ）参考`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`（$ b $ id` INT（10）UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT，
`desc` VARCHAR（65535）NOT NULL，
` （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，
 ```VARCHAR（255），
`desc` VARCHAR（65535），
`price` DECIMAL（5,2）NOT NULL，
 PRIMARY KEY（`id`）
）ENGINE = InnoDB DEFAULT CHARSET = utf8; 
  

有可能创建它？

解决方案

否。

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

所有外键是，是另一个表（或同一个表中的另一个记录）的值，主键，可以在连接中使用。实际上，如果您只需要使用联接的值，您可以引用主键以外的字段。

但是，外键约束会告诉数据库强制执行规则：对于表中的每个外键值，被引用的表都有一个记录，因为它是主键。强制PDF表中的每个外键都有一个主键在所有四个表中不适用于您。因此，请继续使用该字段来引用其他记录，但是不要创建任何外键约束。

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

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.

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.

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