How to define Many to Many linking table

Question

I have a schema with a store and product table. A store record, may have many products, and a product may be available to many stores (many to many) To link these, a third table has the PRIMARY KEY of the store, and product records. Should this linking table, have a compound PRIMARY KEY? Or, should this simply be two FOREIGN KEYS, each with an INDEX()?

CREATE TABLE store(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  PRIMARY KEY(id)
) ENGINE=INNODB;

CREATE TABLE product(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  PRIMARY KEY(id)
) ENGINE=INNODB;

So, should the linking table be like..

CREATE TABLE store_product_link(
  store_id INT NOT NULL,
  product_id INT NOT NULL,
  INDEX(store_id),
  INDEX(product_id),
  FOREIGN KEY (store_id) REFERENCES store(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES product(id) ON DELETE CASCADE
) ENGINE=INNODB;

Or should this be

CREATE TABLE store_product_link(
  store_id INT NOT NULL,
  product_id INT NOT NULL,
  PRIMARY KEY(store_id, product_id)
) ENGINE=INNODB;

It is always good to keep relationship with table – Shibon Jan 02 '20 at 08:15 — Shibon, Jan 02 '20 at 08:15

score 0 · Accepted Answer · answered Jan 02 '20 at 08:07

0

Both, it should have a primary key and both foreign keys.

CREATE TABLE store_product_link(store_id INT NOT NULL, product_id INT NOT NULL,
PRIMARY KEY(store_id, product_id),
FOREIGN KEY (store_id) REFERENCES store(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES product(id) ON DELETE CASCADE) ENGINE=INNODB;

answered Jan 02 '20 at 08:07

simon.ro

2,984
2
22
36

Why should it have a primary key? – Bubinga Aug 06 '20 at 03:05
@Bubinga see https://stackoverflow.com/a/840182/2131212 – simon.ro Aug 06 '20 at 06:45

How to define Many to Many linking table

1 Answers1