StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL - One To One Relation?
text
Body
copied!<p>Now I do know there are some answers about One To One Relation, but neither of them answered my question, so please read this before down vote :)</p> <p>Im trying to achive One To One relation in MySQL database. For example lets say I have Users table, and Accounts Table. And I want to be sure there is 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 dont 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 foreign key in accounts pointing to primary key in users. And then I make foreign key UNIQUE, so there cant 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 foreign key that points from 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 Thats 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 achive this?</p> <p>Thanks!!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload