Geek Answers Handbook

Two main keys

this

here is a graphical representation of my partial database. BrandNo is the primary key in the Suppliar table, which is used as a foreign key in others.

In the LotDetails table, I need BrandName as a foreign key. That sounds absurd, as I can do either

  • One primary key OR
  • Component key

to be used as a foreign key.

I know that I can use BrandNo as a foreign key and Display BrandName, but for the KNOWLEDGE (and yes EASE of the course) I want to know what

Can two table attributes be used as foreign keys separately in different tables?

Editted

BrandNo is only a serial number, and the brand name can be the name of any brand. BrandNo is required in 4 tables, as shown, while BrandName is required in only one table. Thanks!

+6
source share
3 answers

Yes, it is quite possible. Assuming BrandName is the candidate key yourself, in principle, you can refer to it just like BrandNo. In this case, BrandName and BrandNo will not be a composite key, they will both be separate candidate keys.

By convention, and for simplicity and ease of use, only one key is usually selected for the table, which will be used to refer to foreign keys. Usually (not always), which is designated as the "primary" key, but it should not be so if you find good reason for this.

+2
source

Yes! FK doesn’t need to reference PK, you don’t even need to reference an indexed column, but for the sake of relational integrity (and sanity) you have to reference a unique significant column (so we “wanted” to refer to PK or at least a unique clustered indexed column) .

It sounds a little strange, but you can build a relational tableAB containing IdA, IdB and tableA and tableB that reference tableAB of the corresponding columns.

btw: the table does not need to own a PC, but two PCs cannot exist. In general, a table is physically ordered by PK.

+3
source

When you create foreign key references, the key in the referenced table must be the primary key. It makes no sense to have a "partial" link.

Instead, you should have a Brands table in which there is a primary key (maybe BrandId , maybe BrandName - I prefer the first). Then tables that need brand information can directly link to this table.

0
source

More articles:


All Articles