Software

Ask something
Question Locked

Primary Key duplication

0 helpful
2 replies

by vennedeborah

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am creating a database where the primary key will sometime be duplicated, but the data itself will be different; can I do this by index yes allow duplicates? I am using subforms; If I have another record with demographics that are the same and the same account number, but some of the data is different, how can I create it so that the demographic data will be the same, but add the different table content?

Thank you so much in advance.

2 replies

by tony hopkinson

Add another column
Say DemographicID of type integer and make it an autonumber (sometimes called Identity).
Make that your primary key.
Then add an Index with allow duplicates on (AccountID ?)

You can't have a duplicated primary key. It's the mechanism for enforcing firrt normal form. Every row in the table can be uniquely identified.

Note you can have a compound Primary Key

In access select more than one row and then press the key button. Still no duplicates but does this sort of thing.

Say you had a table of telephone numbers landline and mobile. Each Person could have one of each

PersonID
TelephoneType
TelephoneNumber

PersonID and TelephoneType would be the primary key. Both could be duplicated, but no combination could.

Whether you can make use of that depends on your data. There is a limit of the number of columns you can use in a compound key, but it's stupidly high. To me if you go past four, there's something badly wrong with your model, and I don't like to do more than two.

HtHs

PS you can have no primary key, but it's a really really really bad idea.

This discussion is closed to new replies.