Mysql – How to create a one to one relationship

MySQLmysql-workbenchrelational-theory

DROP DATABASE if exists test;
CREATE DATABASE test;
USE test;

CREATE TABLE A(
a1 int not null,
a2 int,
PRIMARY KEY (a1)
);

CREATE TABLE B(
b1 int not null,
b2 int,
PRIMARY KEY (b1)
);

How can I create a one to one relationship between the tables?
I succeeded only in making a One to many relationship:

DROP DATABASE if exists test;
CREATE DATABASE test;
USE test;


CREATE TABLE A(
a1 int not null,
a2 int,
PRIMARY KEY (a1)
);

CREATE TABLE B(
b1 int not null,
b2 int,
x int not null,
PRIMARY KEY (b1,x),
FOREIGN KEY (x) references A(a1)
);

Mysql workbench forward engineering give me these outputs:

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema test
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema test
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET latin1 ;
USE `test` ;

-- -----------------------------------------------------
-- Table `test`.`a`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test`.`a` (
  `a1` INT(11) NOT NULL COMMENT '',
  `a2` INT(11) NULL DEFAULT NULL COMMENT '',
  PRIMARY KEY (`a1`)  COMMENT '')
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test`.`b`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test`.`b` (
  `b1` INT(11) NOT NULL COMMENT '',
  `b2` INT(11) NULL DEFAULT NULL COMMENT '',
  `a_a1` INT(11) NOT NULL COMMENT '',
  PRIMARY KEY (`b1`, `a_a1`)  COMMENT '',
  INDEX `fk_b_a_idx` (`a_a1` ASC)  COMMENT '',
  CONSTRAINT `fk_b_a`
    FOREIGN KEY (`a_a1`)
    REFERENCES `test`.`a` (`a1`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

for this image:
screenshot

and this output:

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema test
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema test
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET latin1 ;
USE `test` ;

-- -----------------------------------------------------
-- Table `test`.`a`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test`.`a` (
  `a1` INT(11) NOT NULL COMMENT '',
  `a2` INT(11) NULL DEFAULT NULL COMMENT '',
  PRIMARY KEY (`a1`)  COMMENT '')
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test`.`b`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test`.`b` (
  `b1` INT(11) NOT NULL COMMENT '',
  `b2` INT(11) NULL DEFAULT NULL COMMENT '',
  `a_a1` INT(11) NOT NULL COMMENT '',
  PRIMARY KEY (`b1`, `a_a1`)  COMMENT '',
  INDEX `fk_b_a_idx` (`a_a1` ASC)  COMMENT '',
  CONSTRAINT `fk_b_a`
    FOREIGN KEY (`a_a1`)
    REFERENCES `test`.`a` (`a1`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

for this image:
screenshot

text-compare.com says the 2 outputs are identical.

Best Answer

To make a one to one relationship, you'll need both join keys to be uniquely constrained. E.g PK to PK or PK to UNIQUE key.

EDIT:

I believe I have already answered your question in this post.

One to one vs One to many (syntax differences)