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.
ETA: Oops! Wrong forum. Mods please move to GQ. :o
You want one (and only one) comment per game? Why not just have ‘comment’ as a column in the Games table?
Also, although I don’t use Access and it’s been a while since I’ve done SQL, what you’ve got should be a one-to-one since GameID is unique and linked on each table.
Yes. The Games table is huge: 1,000,000 records. Each records needs only one comment but there will be only a very few comments. To save space I moved them to a separate table.
Heh, I was expecting to see a pitting of SQL constraints, which often made my life hard the last year or so while writing data synchronization software.
Gotcha.
Why is what you have not 1-to-1? I can’t see how you can create an 1-to-n relationship with that, and I’ve just tried on my database here (postgres) and it does what you want.
test=# select * from games left join comments on comments.gameid = games.gameid;
gameid | status | lastupdate | gameid | comment
--------+--------+------------+--------+------------------
1 | 0 | 2008-01-15 | 1 | this is da bomb
2 | 1 | 2008-01-15 | |
(Apologies if that join isn’t the way to do that, I’m working off distant memory here).
the PC apeman, did you just want to bitch about the situation, (Pit), or were you looking for tech help (GQ)?
The standard way of doing that would either be to not bother,(Not enforce a constraint on one-to-one; only create any entry for comments where appropriate,
Or create a two-way constraint
Games (gameID[pk], commentID[Fk to CommentID in comment] …)
Comment(commentID[pk], gameID[PK to GameID in games… )
Obviously not real code, and if it’s for an assignment, you are setting yourself up to violate normal forms, which instructor may not like.
But that requires both tables to be created in full with empty data, and
ALso you can dead-lock yourself out of creating anything, so you have to turn off constrants on add, then put back on after.
Moved from the Pit to GQ per OP request.
Well after I run the VBA, if I use Access’ Tools/Relationships to view what I’ve created, it shows up as a one-to-many. Which isn’t really a problem - still works for my needs. I guess it’s now become more of a puzzle for me than a practical issue. Thanks for the input.
Not bothering may well be the best answer. No instructor here. This is just the sort of thing I do for fun. (Yes, I am that weird.)
Here’s the back story:
My folks (who are even weirder than I am) LOVE FreeCell. Years ago when they had a Win98 machine I wrote a simple program to track which of the 32,000 possible games they’ve solved. They eventually solved them all.
Now they have a Vista machine and FreeCell has 1,000,000 possible games. I’m writing them a new program to track their progress. Yes, this nut has not fallen far from the family tree.
In that case there is no reason to bother with an enforced constraint. Technically it is one-to-many, but let the outside program do a lookup-edit/add switch. We call that external data integrity management.
Yeah - most database tools don’t quite “grok” a one-to-one relationship. And indeed in most database designs, they’re discouraged. After 25ish years in IT, I don’t think I’ve ever seen one.
Depending on how the database works, simply having a field for comments, with no data in it, won’t take up significant (any?) storage space. Also, with the one-to-one design you’re talking about, then you always have to read two tables to determine if the game has a comment so you’re slowing down your retrievals - you have to read GAME, then you have to (try to) read COMMENT.
So in your scenario you’ve got a trade-off: potential storage space waste, vs. definite access speed slowdown.
FWIW - a “one-to-many” relationship basically means “one to zero, one or more”. In your case the “or more” simply wouldn’t happen because you’d never insert a second comment. Wolfman’s comment about possible deadlock is a valid one also. If you really need to have the separate table, I’d just live with the one-to-many.
Thanks wolfman & SpaceDog. I’ll probably just go the simpler route as you suggest. Curiosity just got the better of me.
(What a bunch of animals we are.)
-apeman
Thanks, Mama Zappa. I’ll test this again with comments in the Games table. I thought it was going to be much larger based on earlier testing. The double lookup isn’t much of a problem though. The comments won’t show up in any reports. That table will only be consulted when a single GameID is being examined via a user interface.
I created a full (1,000,000 records) Games table with an empty comments field. It turned out to be 238 MB vs. 24 MB without the comments field. MS Access probably isn’t the smartest DB.
Ah, interesting. In your situation then, it sounds like the one-to-one is a better option. If you were doing a lot of reporting where you needed comments, the solution might well skew the other way.
I did some searching and wasn’t able to find a “variable length” Access field type (I don’t have Access on my computer and it’s been a while since I’ve worked with it; I was thinking of the Oracle ‘varchar’ field type).
I presume that you have the “required” attribute set to “No” for your comment field, yes? (That’s the equivalent of specifying NULLABLE for a SQL definition.) Also, I didn’t catch what data type you’re using for the field, but it should be Access’ memo type, not a text field.
All text fields in Access are the equivalent of varchar, but are limited to 255 characters. Longer strings have to be stored in “memo” fields, which are essentially “varchar(63999)” fields.
Ah, yes, Access’s Memo field is a hog, isn’t it? I’ve been using Access for years, and I love it, but I miss something like a varchar where you can specify the number of characters. With Access, it’s either 255 characters or 64,000 characters, no in-between (unless Access 2007 has done something with that).
By the way, we use one-to-one relationships in one of our company’s applications. One of the tables contain custom fields for particular clients, so rather than modifying a table, we just attach the custom table to the usual table. Also, we’ve had to split tables when row size became an issue.
ETA: Er, Cerowyn beat me to it.
I can’t find the place in Access to enter the SQL statements directly, but using the design views, it looks like access will only consider a one-to-one relationship when the primary key is an “AutoNumber” type.