Many-to-Many SQL Relationship

Many-to-Many SQL Relationship overview

Generally speaking, a many-to-many SQL relationship is given when an undefined number of rows (n) in a table is related to an undefined number of rows (m) that are stored in another table. This is called a n:m relationship because rows in the first table are related to rows in another one.

For the undefined number of rows, a possible amount of more than one row should be reasonable. If you are sure that only one row from a table is related to n rows in another, a one-to-many (1:n) relationship is given instead.

Here is an example that stores countries and languages in different table. There are countries in the world where more than one official language is spoken and many languages are spoken in more than one country. Therefor, a many-to-many relationship is given:

Many-To-Many (n:m) SQL relationship

As you can see, the main tables Country and Language save the payload. The relationship is saved using another table, a so called junction table. That table stores the primary keys of related rows. The combination is unique and builds the primary key of our junction table itself.

 

Many-to-Many relationship implementation

As you can see in the example above, a many-to-many relationship is implemented with the help of a junction table. The junction table stores the primary keys of each table that is involved in the relationship. By doing this you’re able to store each possible combination of primary keys which itself is unique again. The following SQL statements can be used on MySQL to create the tables from the example above. The syntax probably doesn’t work on every RDBMS:

-- -----------------------------------------------------
-- Table `Country`
-- -----------------------------------------------------
CREATE TABLE `Country` (
  `countryId` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `countryName` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`countryId`) );
 
-- -----------------------------------------------------
-- Table `Language`
-- -----------------------------------------------------
CREATE TABLE `Language` (
  `languageId` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `languageName` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`languageId`) );
 
-- -----------------------------------------------------
-- Table `Country2Language`
-- -----------------------------------------------------
CREATE TABLE `Country2Language` (
  `Country_countryId` INT UNSIGNED NOT NULL ,
  `Language_languageId` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`Country_countryId`, `Language_languageId`) ,
  INDEX `fk_Country_has_Language_Language1` (`Language_languageId` ASC) ,
  INDEX `fk_Country_has_Language_Country` (`Country_countryId` ASC) ,
  CONSTRAINT `fk_Country_has_Language_Country`
    FOREIGN KEY (`Country_countryId` )
    REFERENCES `Country` (`countryId` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Country_has_Language_Language1`
    FOREIGN KEY (`Language_languageId` )
    REFERENCES `Language` (`languageId` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);