SQL: One-to-One Relationship Constraint

Factual Questions
21

[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.

22

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.