Two Primary Keys

Question

here is the pictorial representation of my partial database. BrandNo is a primary key in Suppliar Table, that is being used as Foreign Key in others.

In LotDetails Table I need BrandName as Foreign Key. This sounds absurd as i can make either

1. a Single Primary Key OR
2. a Composite Key

that will be used as Foreign Key.

I know that I can use BrandNo as Foreign Key and Display BrandName, but for the sake of KNOWLEDGE (and yes EASE ofcourse) i want to know that

Is it possible to use two attributes of a table as Foreign Keys separately in different Tables?

EDITTED

BrandNo is just a Serial Number and Brand Name can be the Name of any Brand. BrandNo is needed in 4 Tables as shown, whereas BrandName is needed in only one table. Thanks!

Answer 1

Yes! A FK don't need to reference a PK, you even don't need to reference a indexed column but for the sake of relational integrity (and sanity) you must reference a unique valued column (thus is why we "like" to reference a PK or at least a unique non clustered indexed column).

It's sound a bit weird but you can build a relational tableAB holding IdA, IdB and tableA and tableB referencing tableAB respective columns.

btw: a table don't need to own a PK but cannot exist two PK. In general the table is physical ordered by the PK.

Answer 2

Yes that's quite possible. Assuming BrandName is a candidate key on its own then in principle you can reference it in just the same way as BrandNo. In that case BrandName and BrandNo would not be a composite key, they would both be separate candidate keys.

By convention and for simplicity and ease of use it is usual to choose just one key per table to be used for foreign key references. Usually (not always) that is the one designated as "primary" key but it doesn't have to be so if you find a good reason to do otherwise.