Would it be possible (about relationship in Ms. Access)?

[wannabesmart]

Can I use non primary key as foreign key exactly like no. 1 and 2 in this picture below?
I still remember that my teacher said non primary key can't be used as foreign key,
but I look this in a book and I often look this on internet that non primary key is used as foreign key.


can anyone experts help me?


I'd be really grateful if anybody could help.

[John_G]

Hi -

Yes, you can use the relationships as you have shown, no problem. It's my own opinion, but in the Product table, if ProductCode is relatively short, and uniquely identifies the products, then you can use it as the PK - you don't need ProductID.

Not so for Supplier, though. A supplier name tends to be long, so you should use SupplierID as the PK. In your Purchase table, I would replace SupplierName with SupplierID, and use that in the relationship. You can always use a query or a DLookup to get the supplier name when you need it.

HTH

John

[thebigthing313]

Foreign keys should be a candidate key of another table, but not necessarily the primary key. A candidate key is any field that uniquely identifies a record. Foreign keys should refer to a candidate key that is unique and that cannot be null.

IMO, keep your autonumber primary keys, and design your forms in a way that will display meaningful fields rather than meaningless autonumbers, but will store those autonumber keys.

[Xipooo]

Just to reiterate in case it's not clear.

YES you can use another field on the table for the foreign key relationship, however it MUST be unique and non-nullable. You cannot have any duplicate values in that field and you cannot allow any nulls in that field.

[wannabesmart]

Thank you all!