MySQL - One To One Relationship?

Question

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.

First solution:

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;

Second solution:

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;

Now the questions:

• What is the best way to create One to One relation in MySQL?
• Are there any other solutions other than these two?

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

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.

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

Solution 1

Since Primary Keys are UNIQUE by default, this makes this relation One to One.

No, that makes the relation "one to zero or one". Is that what you actually need?

If yes, then then your "second solution" is better:

• it's simpler,
• takes less storage¹ (and therefore makes cache "larger")
• hes less indexes to maintain², which benefits data manipulation,
• and (since you are using InnoDB) naturally clusters the data, so users that are close together will have their accounts stored close together as well, which may benefit cache locality and certain kinds of range scans.

BTW, you'll need to make accounts.id an ordinary integer (not auto-increment) for this to work.

If no, see below...

What is the best way to create One to One relation in MySQL?

Well, "best" is an overloaded word, but the "standard" solution would be the same as in any other database: put both entities (user and account in your case) in the same physical table.

Are there any other solutions other than these two?

Theoretically, you could make circular FKs between the two PKs, but that would require deferred constraints to resolve the chicken-and-egg problem, which are unfortunately not supported under MySQL.

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

I don't have much practical experience with that particular modeling tool, but I'm guessing that's because it is "one to many" where "many" side was capped at 1 by making it unique. Please remember that "many" doesn't mean "1 or many", it means "0 or many", so the "capped" version really means "0 or 1".

---

¹ Not just in the storage expense for the additional field, but for the secondary index as well. And since you are using InnoDB which always clusters tables, beware that secondary indexes are even more expensive in clustered tables than they are in heap-based tables.

² InnoDB requires indexes on foreign keys.

Solution 2

Your first approach creates two candidate keys in the accounts table: id and user_id.

I therefore suggest the second approach i.e. using the foreign key as the primary key. This:

• uses one less column
• allows you to uniquely identify each row
• allows you to match account with user

Comments

[Limeni]

Thanks! Do you know why does MySQL Workbench EER diagram sees this relation as one -> many? :S

[Limeni]

Found the solution for MySQL Workbench: bugs.mysql.com/bug.php?id=43920 Interesting stuff, its not a bug after all...

[AmericanUmlaut]

I think you might get into trouble using IDs as your foreign key. You can't guarantee that both IDs are identical, because the database auto-generates them (theoretically they should always be the same, but you can't assume it). In fact, since your example doesn't have NOT NULL defined for the account->user relationship, I assume it's possible to have an account without a user or the other way around? If you ever had a situation where you created multiple users, then created their accounts in a different order, your relationship would break.

[Limeni]

@AmericanUmlaut Just as I wanted to ask next :) I assume that the best way to go with One to One using MySQL would be using foreign key and adding unique constrain, like in 1. example

[AmericanUmlaut]

I think that's the cleaner solution. Pragmatically speaking, if you always create and delete both tables simultaneously, using the IDs is probably never going to actually cause a problem, but if you ever hit an edge case where the IDs somehow got out of sync, you could find yourself in a world of pain.

[Salman A]

@AmericanUmlaut: why would you want to auto-generate a foreign key?

[Branko Dimitrijevic]

@AmericanUmlaut Unfortunately, power cuts, hardware failures and bugs are a fact of life. Auto-increment is not transactional, so if any of these problems happens between the first and the second INSERT, it will push the auto-increment out of whack, even though the transaction itself is rolled back.

[Branko Dimitrijevic]

@Limeni My pleasure! BTW, this is an English site, and using your (in this case our) local language is not recommended.

[Amit]

you should have little explained how "primary key" approach will make solution one to (zero or one). It took me second revise to get the idea behind it! However it was informative