MySQL - 一对一的关系?_数据库问题-跟版网

本文介绍了MySQL - 一对一的关系?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着跟版网的小编来一起学习吧！

问题描述

我正在努力实现一对一"MySQL 数据库中的关系.例如，假设我有一个用户表和一个帐户表.我想确保一个用户只能拥有一个帐户.并且每个用户只能有一个帐户.

I'm trying to achieve a "One to one" relationship in a MySQL database. For example, let's say I have a Users table and an Accounts table. I want to be sure that a User can have only one Account. And that there can be only one Account per User.

我为此找到了两个解决方案，但不知道该使用什么，还有其他选择吗.

I found two solutions for this but don't know what to use, and are there any other options.

DROP DATABASE IF EXISTS test;
CREATE DATABASE test CHARSET = utf8 COLLATE = utf8_general_ci;
USE test;

CREATE TABLE users(
    id INT NOT NULL AUTO_INCREMENT,
    user_name VARCHAR(45) NOT NULL,
    PRIMARY KEY(id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE accounts(
    id INT NOT NULL AUTO_INCREMENT,
    account_name VARCHAR(45) NOT NULL,
    user_id INT UNIQUE,
    PRIMARY KEY(id),
    FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

在这个例子中，我在账户中定义了指向用户主键的外键.然后我使外键唯一，因此帐户中不能有两个相同的用户.要连接表，我将使用此查询:

In this example, I define the foreign key in accounts pointing to the primary key in users. And then I make foreign key UNIQUE, so there can't be two identical users in accounts. To join tables I would use this query:

SELECT * FROM users JOIN accounts ON users.id = accounts.user_id;

第二种解决方案:

DROP DATABASE IF EXISTS test;
CREATE DATABASE test CHARSET = utf8 COLLATE = utf8_general_ci;
USE test;

CREATE TABLE users(
    id INT NOT NULL AUTO_INCREMENT,
    user_name VARCHAR(45) NOT NULL,
    PRIMARY KEY(id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE accounts(
    id INT NOT NULL AUTO_INCREMENT,
    account_name VARCHAR(45) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(id) REFERENCES users(id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

在此示例中，我创建了一个外键，该外键从主键指向另一个表中的主键.由于默认情况下主键是唯一的，这使得这种关系是一对一的.要连接表，我可以使用它:

In this example, I create a foreign key that points from the primary key to a primary key in another table. Since Primary Keys are UNIQUE by default, this makes this relation One to One. To join tables I can use this:

SELECT * FROM users JOIN accounts ON users.id = accounts.id;

现在的问题:

在 MySQL 中创建一对一关系的最佳方法是什么?
除了这两个之外还有其他解决方案吗?

我正在使用 MySQL Workbench，当我在 EER 图中设计一对一关系并让 MySQL Workbench 生成 SQL 代码时，我得到一对多关系:S 这让我很困惑 :S

I'm using MySQL Workbench, and when I design One To One relation in EER diagram and let MySQL Workbench produce SQL code, I get One to Many relation :S That's what's confusing me :S

如果我将这些解决方案中的任何一个导入 MySQL Workbench EER 图表，它会将关系识别为一对多 :S 这也令人困惑.

And if I import any of these solutions into MySQL Workbench EER diagram, it recognizes relations as One to Many :S That's also confusing.

那么，在 MySQL DDL 中定义一对一关系的最佳方法是什么.有哪些选择可以实现这一目标?

So, what would be the best way to define One to One relation in MySQL DDL. And what options are there to achieve this?

MySQL - 一对一的关系?

问题描述

第二种解决方案:

推荐答案

相关文档推荐