CODEWITHSUNDEEP
mysqldatabaseforeign-keysrelational-databaserelationships
motia
motia

Reputation: 1999

one-to-one and one-to-many relationships

MySQL Workbench

Using Mysql Workbench, I found that the sql of the one_to_one table and one_to_many table is similar. Both use a non-unique foreign key to implement their relationships.

    CREATE TABLE IF NOT EXISTS `mydb`.`one_to_one` (
      `id` INT NOT NULL,
      `parent_id` INT NOT NULL,
      PRIMARY KEY (`id`, `parent_id`),
      INDEX `fk_one_to_one_parent1_idx` (`parent_id` ASC),
      CONSTRAINT `fk_one_to_one_parent1`
        FOREIGN KEY (`parent_id`)
        REFERENCES `mydb`.`parent` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB

Consequently, I can insert rows in the one_to_one table that reference the same row in the parent table just like the one_to_many table.

Is not this a violation to the one-to-one relationship? Or should I define the one-to-one relationships with a unique foreign key?

Upvotes: 0

Views: 63

Answers (1)

Tung Nguyen
Tung Nguyen

Reputation: 835

Foreign key constraint only checks if a value of parent_id column in table one_to_one exists in parent table or not. You can handle the issue by adding an unique index for parent_id in one_to_one table.

ALTER TABLE `mydb`.`one_to_one` 
ADD UNIQUE INDEX `parent_id_UNIQUE` (`parent_id` ASC);

Upvotes: 1

Related Questions