By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,747 Members | 1,672 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,747 IT Pros & Developers. It's quick & easy.

Preventing Duplicates in a Table

P: 4
I have a very simple table in a database that I want to prevent duplicate entries for as it is a product table. How would I go about that?
Jun 26 '14 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 10K+
P: 12,430
Create a unique index on the columns that identify a unique record.
Jun 26 '14 #2

P: 4
They have unique indexes - what I ran into once is one of my other queries or tables or I don't know what, was creating Products - this may have been due to someone else creating queries in the DB and knowing less than me about what they were doing, but nonetheless, duplicate products were made with different indexes and it caused some issue - none that I couldn't fix, but I'd rather make it where duplicates can't be made accidentally or written by other queries
Jun 26 '14 #3

Expert Mod 2.5K+
P: 3,486
The only way you can prevent that is by programmatically checking each record as it is created so that all fields create a unique record. This can be challengin if you have multiple users who might "fat-finger" things in your forms.

For example, if your products table is for types of meat, one person could type in "Ground Chuck" and another person (or the same person) could later enter "Ground Chunk". That would be a duplicate record, but a misspelled one at the same time.

Additionally, if everything in the record is identical except for one field, it is still considered a unique (i.e. not a "duplicate") record.

Thus is the challenge of maintaining a database!
Jun 26 '14 #4

Expert Mod 10K+
P: 12,430
If you have a unique index, then it's impossible to create a duplicate on that index. What you probably have is a unique index on an autonumber field.

What you need is a unique index on the fields that you use to identify a record as unique. For example, if you use a product name and a manufacturer name to identify a unique product, then you also need a composite unique index on those fields.
Jun 26 '14 #5

Expert Mod 15k+
P: 31,769
As Rabbit says, you need to define a unique index on whatever field(s) it is that determine if it should be considered unique.

If that's an AutoNumber then set that to a unique index and it will never accept duplicates. If that is a bunch of fields that together, and in conjunction, make a record unique, then create a unique index across all of those fields.

If you do this then duplicate records cannot be created by anyone unless and until they remove the index.

As long as you know what you mean by unique (because that could mean different things to different people even for the same table), and define an index to match, you should need nothing else.
Jun 26 '14 #6

Post your reply

Sign in to post your reply or Sign up for a free account.