I have a table called User that has UserName as a primary key.

I want to establish a relationship with enforced referential integrity between User and FriendRequest.

FriendRequest has the following: RequestID, UserNameA, UserNameB, Accepted. UserNameA is the sender of the request and UserNameB is the recpient. Accepting requests sets the relationship between A and B to friends. Rejecting deletes the row. No answer means that Accepted is set to FALSE.

The problem is that I can't enforce integrity and without it I can enter any user name for UserNameA and UserNameB even if that particular UserName does not even exist on UserProfile.

Is there a way to make my FriendRequest table work in Access?

enter image description here

In this screenshot, before I added the second row for relationships, the relation type was displayed as one-to-many. As soon as I add the second relationship it becomes one-to-one.


Alright, I did it again (carefully this time). You were right that I did only create one relationship instead of two. However, I get this wierd duplication of the same table. Please take a look at the second screenshot. As you can see UserProfile was duplicated to UserProfile_1. But my list of actual existing tables only show that UserProfile exists. What exactly is UserProfile_1?

enter image description here

  • 2
    What's the problem? Create two foreign key constraints on tble FriendRequest (referencing User). What doesn't? – tombom Dec 1 '14 at 13:04
  • 1
    You need to add 2 foreign keys, as @tombom suggested. One FK for UserNameA REFERENCES Users (UserName) and another for UserNameB REFERENCES Users (UserName). There surely is a way to do this in Access. – ypercubeᵀᴹ Dec 1 '14 at 13:17
  • 1
    What do you mean with "not work"? What did you try and what error did you get? – ypercubeᵀᴹ Dec 1 '14 at 13:38
  • 1
    Oh, I see the image now. What you did was trying to create one relationship with 2 columns. Don't do that. First create one relationship, only with UserNameA and then another one, with UserNameB. – ypercubeᵀᴹ Dec 1 '14 at 13:40
  • 1
    You can answer it yourself by moving the "EDIT" part to an answer. – ypercubeᵀᴹ Dec 4 '14 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.