CODEWITHSUNDEEP
sql-server
Nate Pet
Nate Pet

Reputation: 46282

SQL Server table PK and FK

I am creating a relationship between 2 tables:

The relationship I like to form is between the Inventory an InventoryExtended tables.

The primary key for the Inventory table is InvID (Inventory ID).

The reason why I created the InventoryExtended is becauses only 1% of the inventory items in the Inventory table will need additional or extended fields, the rest will not. Instead of adding these additional fields in the Inventory table where 99% will be blank for 50 additional fields that I need I decided to create an InventoryExtended table and store the 50 fields there.

The relationship between the Inventory an InventoryExtended table will be 1 to 1.

Meaning, for the 1% of the records in the Inventory table , the InvId will be the same as the InvId in the InventoryExtended table.

My question is that should the InvID in the InventoryExtended table be a FK (Foreign Key) or should it be a PK and a FK?

I am thinking it should be a PK and a FK as there the InvID will be unique in the InventoryExtended table.

Thanks in advance.

Upvotes: 2

Views: 858

Answers (2)

alok
alok

Reputation: 1340

The InvID will be both a PK and FK for the extended table.

If you know in advance (meaning when you attempt to fetch data) whether or not a particular inventory type will have the extended data, for such records you can even skip the original table altogether and simply use two disjoint tables smallInventory and bigInventory such that no records of one are present in the other.

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115620

You are correct.

The InvID should be a PRIMARY KEY and a FOREIGN KEY as it will be unique in the InventoryExtended table.

This type of relationship is indeed 1:1 or (more accurately) 1::0..1, as only some of the rows in the Inventory table will have a related row in InventoryExtended.

Also note that the InventoryExtended (InvID) should not have the IDENTITY property, even if the Inventory (InvID) has it.

Upvotes: 2

Related Questions