mysql - Is it possible to reference one column as multiple foreign keys?

Question

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?

Answer 1

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.