Share via

How do I use two columns joined together as a foreign key?

Anonymous
2021-08-01T09:38:57+00:00

Hello, please how can I reference a column which is a merger of two columns in another table? Take for instance I have these tables:

Create table products1(

itemNo int not null identity(1001,1)

itemGroup int not null,

itemLoc AS CONCAT(itemGroup, itemNo)

Primary Key(itemLoc)

)

Run.

Here's another table:

Create table customers1(

customerID int not null (50002,1)

pk_itemLoc /* (I don't know the right datatype to use here)*/

Foreign key(pk_itemLoc REFERENCES products1(itemLoc)

)

In the first table named Product1, I want the primary Key to be itemLoc, and itemLoc to be a merger of itemGroup and itemNo. ItemGroup and itemNo are of integer data type.

What data type should pk_itemLoc which is a foreign key in table Customers1 be?

Looking at my code, all I need is just the right line of code for the itemLoc in products1 table and the right data type for pk_itemLoc column.

No lecture needed, just the line of code. Thank you

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

21 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-08-01T20:25:49+00:00

    I believe strongly you people can help me with what I want. And I still believe you love making someone look STUPID.

    It doesn't matter what the standard says, first it is expected if there's a solution to achieve what I wanted irrespective of the consequences. I am the one that needs it, and I know what I want to do with it.

    I made it categorically clear that I WAS USING AN EXAMPLE.

    I shared codes and pleaded you study it and write a line that can make me achieve my desire but you kept on TEACHING AND TEACHING what is unrelated to my question.

    Now, I have gotten what I want which I still strongly believe you know, and it's a simple thing to you. You never approached my question and desire with open mind but with a perception that you're an EXPERT and one MUST FOLLOW only your pattern of approach. I am disappointed.

    Programmers are thinkers, they must not follow a particular, what matters most is the end result.

    I said I needed a line of code to help make a column which is as a result of two other columns with integer type concatenated be used as a primary key such that when it is being referenced in another column as a foreign key, I wouldn't face any barrier of the data type in the foreign key table of being different from what is in the primary table. That I believe should be simple to you all

    You should have given me the code first before probing further. Naaah, I never expected these from you all as experts. It's because I believe you are an expert that is the reason I am most pained

    I am attaching photos of the codes that resolved it. Will you tell me you don't know about these codes?

    Messrs @Daniel Pineault, Tom, Grover, ScottDiamond, ScottGemImageImage

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-08-01T19:57:10+00:00

    I appreciate your attention and time. In order for me not to be misunderstood, I shared the codes. But no problem. I will try the best and can.

    I am sure you people know how I can resolve it, I just think you've not understood my aim better.

    All the same, I appreciate everyone of you. It's not easy taking time out of one's busy schedule to help another resolve his issue. I know every programmer is usually busy.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-08-01T19:39:24+00:00

    You might like to take a look at Relationships.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file illustrates the use of multi-column keys in building up a model.  It uses a simplified medical prescriptions database as its example. One thing to note about the use of composite 'natural' keys is that this can increase the efficiency of the database by virtue of reducing the number of tables needed in queries compared with the use of 'surrogate' autonumber keys.

    It is not necessary to concatenate values in a single column to create a candidate key.  On the contrary this introduces redundancy and the consequent risk of update anomalies.  Such a table would not be normalized to First Normal Form (1NF), whose definition is:

    First Normal Form: A relvar is in 1NF if and only if, in every legal value of that relvar, every tuple contains exactly one value for each attribute.

    Loosely speaking, in the language of the relational model, a relvar (relation variable) equates to a table (or more accurately a table definition), a tuple to a row (record) and an attribute to a column (field).

    Was this answer helpful?

    0 comments
  4. Anonymous
    2021-08-01T19:37:41+00:00

    We are not trying to make you look stupid and clearly you are not. For reasons you refuse to explain you want to go against the rules and logic of good database design. If you hired an architect to build a house and you wanted to go against building codes they would refuse. If you hired a plumber and asked them to go against standard design, most reputable plumbers would refuse the job. You are asking us to help you create a faulty database design and without even explaining why you want to.

    When you consult an expert, doesn't it make sense to listen to that expert's advice? I'm sorry that you refuse to listen to our advice and wish you luck with your project.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2021-08-01T19:37:23+00:00

    I have never seen it. I have never done so. This would be what is be trying if ever I had such a need, no guarantee it will work, just an idea.

    Was this answer helpful?

    0 comments