Establishing a simple relationship?

[Harry2]

Hi everyone,

I am trying to establish a relationship between two tables - and enforce referential integrity.

I have two tables: A and B.

However, there is data in Table A which is not in Table B and vice versa.



Can anyone recommend an approach in which I am able to enforce referential integrity between the two tables?


Many Thanks,

H

[June7]

Don't understand this relationship. What are the PK/FK fields? Is one of these tables a 'lookup' source for data entry into other table?

[Harry2]

Hi June 7,

-No, one of the table's is not a lookup source.

Table A: Social security number (PK), first name, last name, address.

Table B: Social security number (PK), email address

Table A and Table B do not have the exact population, there is some overlap where some people are in table A who are not in Table B. And there are some people in Table B who are not in Table A.

However, in the future more email addresses will be sourced and added into Table B.
Also, some people added into Table A.

Do you know a relationship / method that can be built into Access to incorporate this kind of scenario?

Kind Regards,
H

[June7]

No, I do not. If you are allowing unrelated records then referential integrity is already violated. Why not 1 table?

[Harry2]

Ah I see what you're saying..

Could a third table be built (Table C) which would contain all the PK's of table A and lookup the email addresses from Table B.
Thus, Table A could link to Table C via a one-one relationship, while also enforcing referential integrity?

This is just a scenario I am thinking of, where there would have to be two tables (in reality these tables would contain hundreds of fields each and therefore it would be more sensible to separate them for ease of updating).
Thus, I would not be able to just add the email address field into Table A - but I see where you're coming from.

Sorry, I'm new to Access - if I'm not making sense!

[June7]

A table with 'hundreds' of fields has the scent of non-normalized data structure.

Your third table concept accomplishes nothing and really doesn't make sense and does not enforce relational integrity of TableB. Tables don't do 'lookup'. Use a query, don't build another table.

You said there are records in TableB with no relationship to records in TableA. A table or query of only the TableA PK's would not retrieve those unrelated records.

[Harry2]

Ah thanks, starting to get a clearer picture now!

The issue is that I have two tables with records in Table A with no relationship to Table B?
e.g. the PK 111 in Table A does not have a corresponding PK 111 in Table B.

However, say in the future, when I source more data, I may come across this PK 111 and add it to Table B.
Therefore, the PK 111 in Table A would now have a corresponding PK 111 in Table B.

That's basically the issue I'm having

[June7]

What referential integrity do you want to manage?

Examples:

1. An Orders table cannot reference a product that is not listed in Products table but products can be listed in Products table that have not yet been associated with an order - can delete the order but products remain in Products table

2. A Contacts table can have a record (contact name, address, phone) but no related record(s) in the Emails table. Could not be an EMail record without related Contact record because if contact is deleted its associated email record(s) also delete (Cascade Delete).