mysql - 外键，障碍多多有用？

Question

我使用mysql workbench来设计数据库。我使用该工具在表之间制作漂亮的线条，这会自动为我添加外键。

这是我的 2 个创建语句:

CREATE TABLE IF NOT EXISTS `mydb`.`PROFILE` (
  `user_id` INT NOT NULL,
  `some_int` BIGINT NULL COMMENT 'an int',
  PRIMARY KEY (`user_id`),
  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`USER_ACCOUNT` (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `field1` VARCHAR(100) NOT NULL,
  `field2` CHAR(32) NOT NULL,
  `PROFILE_user_id` INT NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE INDEX `email_address_UNIQUE` (`email_address` ASC),
  INDEX `fk_USER_ACCOUNT_PROFILE1_idx` (`PROFILE_user_id` ASC),
  CONSTRAINT `fk_USER_ACCOUNT_PROFILE1`
    FOREIGN KEY (`PROFILE_user_id`)
    REFERENCES `mydb`.`PROFILE` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

我无法插入一个而不遇到像 ERROR 1452: Cannot add or update a child row: a foreign key constraint fails 这样的事情- 我尝试先插入 child ，但似乎没有什么区别。

然后我想到，由于我绘制了 1:1 关系，因此该键必须意味着对于 PROFILE 中的每个条目，我在 USER_ACCOUNT 中都有一个条目 - 否则外键的意义何在？使用这个想法，我首先插入到子级中，然后使用相同的 user_id 为父级编写更新查询，更新不起作用。

所以我的第一个问题是，这个外键有什么意义？我知道表之间存在 1:1 关系，那么我应该强制执行吗？

其次，我该如何插入呢？我自己的外键不允许我插入数据。

谢谢，任何帮助都会很棒，我对数据库设计有点生疏。

Answer 1

首先，您不需要在 PRIMARY KEY 上使用 UNIQUE 索引，PRIMARY KEY 已经强制执行了这一点。

其次，从概念上讲，您已将外键添加到错误的表中，因为配置文件应该引用用户。这可能就是您的问题的根源。

我建议使用以下 DDL:

CREATE TABLE IF NOT EXISTS `mydb`.`USER_ACCOUNT` (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `field1` VARCHAR(100) NOT NULL,
  `field2` CHAR(32) NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE INDEX `email_address_UNIQUE` (`email_address` ASC)
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`PROFILE` (
  `user_id` INT NOT NULL,
  `some_int` BIGINT NULL COMMENT 'an int',
  PRIMARY KEY (`user_id`),
  CONSTRAINT `fk_PROFILE_USER_ACCOUNT1`
    FOREIGN KEY (`user_id`)
    REFERENCES `mydb`.`USER_ACCOUNT` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

并将您的用户插入USER_ACCOUNT，然后插入PROFILE。

问。我应该强制执行外键吗？

A.几乎可以肯定，它可以防止您的数据陷入不一致的状态。可以添加具有已删除用户或不存在用户的配置文件。了解数据始终一致可以让查询、显示数据和调试变得更加容易，因为无需担心。