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;
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;
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)