Referential Integrity

[delbert]

I have two tables. The first table has a primary key called customer
code. The second table has a primary key consisting of the two fields,
"customer code and piece code". When I join the two tables with
customer code, I cannot choose "Enforce Referential Integrity". I
assume this is because the customer code key is part of two keys in
the second table. My question is: will my database be safe even
though I have not been able to enforce Referential Integrity.

 

[John W. Vinson]

I have two tables. The first table has a primary key called customer
code. The second table has a primary key consisting of the two fields,
"customer code and piece code". When I join the two tables with
customer code, I cannot choose "Enforce Referential Integrity". I
assume this is because the customer code key is part of two keys in
the second table. My question is: will my database be safe even
though I have not been able to enforce Referential Integrity.

No.

I don't see why you can't enforce RI. As described your structure is perfectly
correct - the second table has a two-field joint primary key, one field of
which is a foreign key to the customer code. What error do you get when you
try to set RI? Is the CustomerCode field of the same size and same datatype in
both tables?

John W. Vinson [MVP]

 

[Guest]

Delbert,

You should be able to create ref. intergrity if you can't then you probably
have different datatypes which don't join correct. Let me take a guess here:

Customer: autonumer
Customer:?

Customer should be Autonumber (if you have used autonumber)
Customer (other table) should be number (than you can link the two with
ref.integrity despite the two keys.

So take another look at the datatypes and make sure the are joinable.

hth

 

[George Nicholson]

you've already gotten responses suggesting you check the structure of the 2
tables to make sure the data types of the 2 fields are the same.

If you already have data in those tables, the list of things to check gets
longer. Make sure your second table:
1) doesn't have any Nulls for CustomerCode (not likely if its part of a
combination primary key)
and
2) doesn't contain any CustomerCode values that aren't in your First table
(via an "Unmatched" query)

Either of the above would prevent referential integrity from being
established on tables with existing data until you bring the offending
records into compliance with the rules you are trying to apply.

If you are still having problems, knowing what error message you get when
you try to join the fields would be a big help. Otherwise, everyone here is
just making guesses about what you are experiencing.

HTH,