Share via

Concatenated foreign key

Anonymous
2021-07-30T03:06:00+00:00

Split from this thread.

Hello Mr. Tom, can you guide me on how to reference a concatenated column as a foreign key in another table?

At first it said I can't reference a non-persisted and computed column (i.e the concatenated column.

So I dropped the table (since it's new), created another table and added PERSISTED at the end of the concatenated column line which resolved that.

Presently, when I run the table code having the foreign key, it says the datatype for the foreign key referencing the concatenated column which is the primary key of the other table is wrong.

I believe there must be a way out. I don't want to use the IDENTITY column as the primary key for some reasons.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

23 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. George Hepworth 22,855 Reputation points Volunteer Moderator
    2021-07-30T12:22:26+00:00

    You want to create a concatenated column in a table to serve as a foreign key? Seems a bit unusual to say the least. How about you just use two fields for the foreign key as the usually the way it's done? Millions of tables in databases use a composite Primary Key and composite Foreign Key. I'm not saying that is the "only" way to do it, but if something has worked successfully for a very long time for a huge number of people, it's a good indication that it is the "most effective" way to do it.

    I found one reference suggesting you can use a persisted concatenated column for a foreign key IF it is indexed, but then the Primary Key in the other table would also have to be the same concatenation of columns and also indexed.

    I once heard an extremely useful analogy about using Access. It goes like this.

    Using Access is somewhat like taking a canoe trip on a swift moving river. However, if you want to go upstream against the current, you'll be paddling hard all the time to make progress. If you go downstream with the current, you only need to use the paddle to make course corrections.

    If you insist on "paddling upstream" by using concatenated primary and foreign keys, expect to invest a lot more of YOUR time in doing it.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. Anonymous
    2021-07-30T06:03:02+00:00

    My Scott, I am very sorry for the inconvenience. I now understand your forum doesn't allow discussing a topic different from an initial topic. I have learned and will refrain from it.

    Thanks for your effort and patience. I will improve on this so it doesn't repeat itself

    Was this answer helpful?

    0 comments
  4. ScottGem 68,820 Reputation points Volunteer Moderator
    2021-07-30T03:08:00+00:00

    AGAIN!. Please stafrt a NEW thread when you have a separate question. This is the second time I've had to split your new question to a new thread. Asking a new question in an unrelated thread makes it more difficult to follow the thread.

    I am not a fan of composite keys and would definitely NOT use a concatenated value as a foreign key.

    Was this answer helpful?

    0 comments
  5. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2021-07-30T01:58:20+00:00

    Well now you're telling us :-) I went back in the threads and did not find where you divulged such important information. I know, hard to know what we deem important, but SQL Server BE is definitely one of them.

    The Create Table statements you are showing are not system versioned tables. Read up about them, and on how to turn an existing table into a SV table.

    Was this answer helpful?

    0 comments