2

I have a database with 6 tables. I tried to view the relationships between the tables by using EER diagram. I observed that all the one-to-one relationships were converted into one-to-many relationships. To be double sure, I started with a new EER diagram, made all the tables and assigned the relationships. Then I forward engineered the model and got the SQL script for the model. The code of this script is shared below. Now again starting from a new model, I went to file->import->reverse engineer create script and checked the option saying 'place imported objects on diagram'. To my great surprise, these two EER diagrams were not the same. The only change being, the one-to-one relationships were replaced by one-to-many relationships. Please tell me if I am wrong at some step, or is it a bug?

P.S- I am using MySQL workbench 8.0.22 on windows 10(64 bit)

-- 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 test
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema test
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `test` ;
USE `test` ;

-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table `test`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test`.`user` (
  `user_id` INT NOT NULL,
  `email` VARCHAR(255) NULL,
  `password` VARCHAR(32) NOT NULL,
  `phone` INT NOT NULL,
  `current_location` VARCHAR(45) NULL,
  PRIMARY KEY (`user_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test`.`agent`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test`.`agent` (
  `agent_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  PRIMARY KEY (`agent_id`),
  INDEX `fk_agent_user1_idx` (`user_id` ASC),
  CONSTRAINT `fk_agent_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `test`.`user` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test`.`consumer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test`.`consumer` (
  `consumer_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  PRIMARY KEY (`consumer_id`),
  INDEX `fk_consumer_user1_idx` (`user_id` ASC),
  CONSTRAINT `fk_consumer_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `test`.`user` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test`.`request`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test`.`request` (
  `request_id` INT NOT NULL,
  `consumer_address` VARCHAR(45) NOT NULL,
  `agent_lat_long` VARCHAR(15) NULL,
  `destination_lat_long` VARCHAR(15) NULL,
  `destination_address` VARCHAR(45) NOT NULL,
  `destination_phone` INT NOT NULL,
  `agent_rating` INT NULL,
  `consumer_rating` INT NULL,
  `date_time` DATETIME NOT NULL,
  `agent_agent_id` INT NOT NULL,
  `consumer_consumer_id` INT NOT NULL,
  PRIMARY KEY (`request_id`),
  INDEX `fk_request_agent1_idx` (`agent_agent_id` ASC),
  INDEX `fk_request_consumer1_idx` (`consumer_consumer_id` ASC),
  CONSTRAINT `fk_request_agent1`
    FOREIGN KEY (`agent_agent_id`)
    REFERENCES `test`.`agent` (`agent_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_request_consumer1`
    FOREIGN KEY (`consumer_consumer_id`)
    REFERENCES `test`.`consumer` (`consumer_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test`.`login`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test`.`login` (
  `login_id` INT NOT NULL,
  `jwt_token` VARCHAR(45) NULL,
  `firebase_token` VARCHAR(45) NULL,
  `user_id` INT NOT NULL,
  PRIMARY KEY (`login_id`),
  INDEX `fk_login_user1_idx` (`user_id` ASC),
  CONSTRAINT `fk_login_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `test`.`user` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test`.`rating`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test`.`rating` (
  `rating` INT NOT NULL,
  `feedback` VARCHAR(45) NULL,
  `user_id` INT NOT NULL,
  `request_id` INT NOT NULL,
  INDEX `fk_rating_user1_idx` (`user_id` ASC),
  PRIMARY KEY (`user_id`, `request_id`),
  INDEX `fk_rating_request1_idx` (`request_id` ASC),
  CONSTRAINT `fk_rating_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `test`.`user` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rating_request1`
    FOREIGN KEY (`request_id`)
    REFERENCES `test`.`request` (`request_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
 
0
3

The relations, as implemented, are not 1-1. So the tool is correctly showing those as one-to-zero/many, because they are. For example, the data model would not prevent me from assigning more than one agent_id to a single user_id.

INSERT INTO test.agent (agent_id,user_id)
VALUES (1,1), (2,1), (3,1), (4,1), (5,1)
;

The correct way to implement 1-1 relations (non-exclusive subtypes in your example) is to ensure the primary key of the subtype is the primary key of the parent:

CREATE TABLE test.agent 
(
  agent_id INT NOT NULL,
  CONSTRAINT PK_agent PRIMARY KEY (agent_id),
  CONSTRAINT FK_agent_is_user FOREIGN KEY (agent_id) REFERENCES test.user (user_id)
)
;

This answer on SO covers the topic in detail.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.