我正在尝试在MySQL数据库中实现“一对一”的关系。例如,假设我有一个Users表和一个Accounts表。我想确保一个用户只能拥有一个帐户。每个用户只能有一个帐户。
我找到了两个解决方案,但是不知道该使用什么,还有其他选择。
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;
在此示例中,我在指向用户主键的帐户中定义外键。然后,我使外键成为唯一键,因此帐户中不能有两个相同的用户。要联接表,我将使用以下查询:
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;
在此示例中,我创建了一个外键,该外键从主键指向另一个表中的主键。由于默认情况下主键是UNIQUE,因此将这种关系一对一。要联接表,我可以使用以下命令:
SELECT * FROM users JOIN accounts ON users.id = accounts.id;
现在的问题:
我正在使用MySQL Workbench,当我在EER图中设计一对一关系并让MySQL Workbench生成SQL代码时,我得到了一对多关系:S那就是让我感到困惑的:S
而且,如果我将这些解决方案中的任何一个导入到MySQL Workbench EER图中,它就会将关系识别为“一对多:S”,这也很令人困惑。
因此,在MySQL DDL中定义一对一关系的最佳方法是什么。有什么选择可以实现这一目标?
由于默认情况下主键是UNIQUE,因此使此关系一对一。
不,这使关系“一对零或一个”。那是您真正需要的吗?
如果 是 ,那么您的“第二个解决方案”更好:
顺便说一句,您需要创建accounts.id一个普通的整数(而不是自动递增)以使其起作用。
accounts.id
如果 否 ,请参见下文…
在MySQL中创建一对一关系的最佳方法是什么?
好吧,“最佳”是一个重载的词,但是“标准”解决方案将与任何其他数据库相同:将两个实体(在您的情况下为用户和帐户)放在同一物理表中。
除了这两个以外,还有其他解决方案吗?
从理论上讲,您可以在两个PK之间进行循环FK,但是这将需要 延迟 约束来解决“鸡与蛋”问题,不幸的是,MySQL不支持该约束。
而且,如果我将这些解决方案中的任何一个导入到MySQL Workbench EER图中,它就会将关系识别为一对多:S那也令人困惑。
我没有使用该特定建模工具的大量实践经验,但是我想这是因为它是“一对多”的,其中“很多”面通过使其唯一性而被限制为1。请记住,“很多”并不表示“ 1或很多”,而是“ 0或很多”,因此“封顶”版本实际上表示“ 0或1”。
1 不仅在附加字段的存储费用中,而且在二级索引中也是如此。并且由于您使用的InnoDB总是对表进行聚类,因此请注意,聚簇表中的二级索引甚至比基于堆的表中的二级索引还要昂贵。
2 InnoDB需要外键索引。