3

I want to know if there is a way to have multiple values in a single field in a MySQL database where each value is a foreign key referencing one other table.

I am designing a database with a product table and a product certification table.

I am using InnoDB and foreign key constraints.

The "product" table contains the details about specific instances of the product. One of the details contained in the product table is the column “product_certification_id”, which is a foreign key referencing an index in the two column “product_certification” table.

The product certification table contains the possible certifications that an instance of a product may have.

My problem stems from the fact that the product certifications are not mutually exclusive, so I am curious if it is possible to have multiple foreign key values in the same field referencing the same table.

Also, I am concerned about the possibility of more certifications being added in the future, so I need to design this in an easily scalable fashion in that sense.

Thank you for your thoughts.

user898750
  • 43
  • 4
  • I think you don't. You can use CSV syntax to store many ids in a field, But you can't use foreign key functions built in MySQL. You have to use a join table to have a many-to-many relationship between "product" and "product_certification" – Elorfin Aug 17 '11 at 14:10

3 Answers3

4

What you typically do is set up a many to many relationship with an intermediate linking table. Some thing like the following:

CREATE TABLE product (
  `id` integer AUTO_INCREMENT NOT NULL,
  -- other cols --
  PRIMARY KEY (`id`)
);

CREATE TABLE certification (
  `id` integer AUTO_INCREMENT NOT NULL,
  -- other cols --
  PRIMARY KEY (`id`)
);

CREATE TABLE product_certification (
   `product_id` integer NOT NULL,
   `certification_id` integer NOT NULL,
   PRIMARY KEY (`product_id`, `certification_id`),
   CONSTRAINT `product_id_product_id` 
     FOREIGN KEY (`product_id`) 
     REFERENCES `product` (`id`) ON DELETE CASCADE,
   CONSTRAINT `certification_id_certification_id` 
     FOREIGN KEY (`certification_id`) 
     REFERENCES `certification` (`id`) ON DELETE CASCADE
);
prodigitalson
  • 60,050
  • 10
  • 100
  • 114
0

If I understand you correctly, the relation product : product_certification is 1:M you can create a foreign key from product_certification to products via product_id, instead of having product_certification_id in the products table (which is invalid, since the product can have more than 1 certification)

Maxim Krizhanovsky
  • 26,265
  • 5
  • 59
  • 89
0

A single field cannot be a foreign key to more than one field in another table. It's just not possible. Since your foreign table has a composite key, your table in question would have to have the same fields as well.

Marc B
  • 356,200
  • 43
  • 426
  • 500