CODEWITHSUNDEEP
databasems-access
Black Ops
Black Ops

Reputation: 3

Question on Microsoft Access - Cannot link primary and foreign key

I have come across a question concerning Primary keys & Foreign keys in MS Access. I do have two tables, Customer and Sales.

Customer has three attributes:
CustomerID (short text which is my primary key),
FirstName (short text)
LastName (short text)

Sales has the following attributes:
SalesID (Number) is the Primary key
ProductID (number) should be a foreign key
CustomerID (short text) should be a foreign key
(...)

Now here is my problem: there can be duplicates of the CustomerID as they can buy several items. I cannot set the foreign key CustomerID in the Sales Table to the primary key CustomerID in the CustomerTable because there can be duplicates.

Do you have any ideas how to solve this problem?

Upvotes: 0

Views: 120

Answers (1)

June7
June7

Reputation: 21370

A related child (dependent) table would normally have duplicates of parent primary key. This is the nature of 1-to-many or many-to-many relationship. Duplication of parent key values in dependent table is not the problem, database structure is.

If each sale can have multiple items, then you need another table called SaleDetails with fields:

SalesID_FK (number - long)
ProductID_FK (number - long)
Quantity (number - long)

Sales table would have:

SaleID_PK (autonumber)
CustomerID_FK (number - long)
SaleDate (date/time)

Use autonumber for primary key in Customers:

CustomerID_PK (autonumber)
CustomerAcct (short text)
etc

Upvotes: 1

Related Questions