title: MySQL - One To One Relationship?
body: <p>I'm trying to achieve a &quot;One to one&quot; 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.</p>
  <p>I found two solutions for this but don't know what to use, and are there any other options.</p>
  <h3>First solution:</h3>
  <pre><code>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;
  </code></pre>
  <p>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:</p>
  <pre><code>SELECT * FROM users JOIN accounts ON users.id = accounts.user_id;
  </code></pre>
  <h3>Second solution:</h3>
  <pre><code>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;
  </code></pre>
  <p>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:</p>
  <pre><code>SELECT * FROM users JOIN accounts ON users.id = accounts.id;
  </code></pre>
  <p>Now the questions:</p>
  <ul>
  <li>What is the best way to create One to One relation in MySQL?</li>
  <li>Are there any other solutions other than these two?</li>
  </ul>
  <p>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</p>
  <p>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.</p>
  <p>So, what would be the best way to define One to One relation in MySQL DDL. And what options are there to achieve this?</p>