# SQL: One-to-One Relationship Constraint

**URL:** <https://boards.straightdope.com/t/sql-one-to-one-relationship-constraint/433612>\
**Category:** Factual Questions\
**Created:** [January 15, 2008, 3:08pm UTC](https://boards.straightdope.com/t/sql-one-to-one-relationship-constraint/433612 "2008-01-15T15:08:36Z")\
**Posts on this page:** 2\
**Page:** 2

<div class="post-metadata">

**Author:** ![Clothahump](https://avatars.discourse-cdn.com/v4/letter/c/51bf81/32.png) [@Clothahump](https://boards.straightdope.com/u/Clothahump)\
**Post date:** [January 15, 2008, 10:21pm UTC](https://boards.straightdope.com/t/sql-one-to-one-relationship-constraint/433612/21 "2008-01-15T22:21:01Z")

</div>

[QUOTE=the PC apeman]  
Amateur SQL user here. I’m trying to define two tables in MS Access with a one-to-one relationship using VBA & SQL. Here’s what I’ve got so far…

CREATE TABLE Games (GameID long PRIMARY KEY, Status byte DEFAULT 0, LastUpdate datetime)  
CREATE TABLE Comments (GameID long PRIMARY KEY CONSTRAINT PK\_GameID REFERENCES Games (GameID), Comment memo)But this produces a one-to-many relationship. What am I doing wrong? Thanks for helping.  
[/QUOTE]

Technically, the relationship is one-to-many. However, by defining the same identical value as the primary key in the second table, you have made it one-to-one, since only one record with that id can exist.

---

<div class="post-metadata">

**Author:** ![the\_PC\_apeman](https://avatars.discourse-cdn.com/v4/letter/t/ac8455/32.png) [@the\_PC\_apeman](https://boards.straightdope.com/u/the_PC_apeman)\
**Post date:** [January 15, 2008, 11:37pm UTC](https://boards.straightdope.com/t/sql-one-to-one-relationship-constraint/433612/22 "2008-01-15T23:37:46Z")

</div>

It turns out that “memo” is not so bad after all. I put the Comment field back into the Games table. Then right after creating the 1,000,000 records with empty Comment fields I compacted the database. This brings it down to 60 MB. So I guess the whole thing was just a fun exercise. Thanks and sorry.

**Clothahump** , good point. The Relationships viewer didn’t figure that out.

I wish there was a way to create the table and relationships via the GUI and then [del]steal[/del] view the SQL equivalent.

[Previous page](https://boards.straightdope.com/t/sql-one-to-one-relationship-constraint/433612.md?page=1)