duplicate values in table.

[nim180]

Hi everyone,

I have 4 fields in my table, i want to set the "mainlocation" field as the primary key but it wont let me, it gives me this error:

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to allow for duplicate entries."

i am pretty new to databases so it would be great if someone could help me with this small problem. i have tryed the answer given in the FAQ but it didnt work.

Regards,
Nim

 

[jsteph]

Use the Find Duplicates Query Wizard. Go to Query,New,FindDuplicates.
Select the Mainlocation field, then in next pane put the rest of the fields.
--Jim

 

[nim180]

Hi Jim,
I did that alright and still it gives me the error thats why im so confused about it. ive changed the index to allow dups as well but when i try to add the "mainlocation" as the primary key that error appears and resets the index back to Yes (No Duplicates).

regards,
nim

 

[lespaul]

You can't have a duplicate primary key - defeats the purpose. A primary key, by defition, is a UNIQUE IDENTIFIER. How if it's a unique identifier can you have TWO of them in the same table? Consider:

Customer (Table)
CustomerID (PK)
CustomerName
CustomerAddress

If you allow two customers with CustomerID 5, how do you know which one you really mean?

Have you read the Fudamentals article linked below?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[jsteph]

Nim,
That's only *half* the issue--you then have to either delete those dups or add a sequence number field in order to make it unique.
--Jim

 

[nim180]

hi everyone,

i see, i shall take a read of that, thanks for yer help