Question on Microsoft Access - Cannot link primary and foreign key

0
votes

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?

1
databasems-access
I cannot set the foreign key CustomerID in the Sales Table to the primary key CustomerID in the CustomerTable because there can be duplicates yes you can. This is the purpose for the foreign key. - forpas
Please provide some sample data to illustrate the issue. - Dale K
Why use text fields as PK and FK? Are tables in Access or SQLServer? - June7

1 Answers

1
votes

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