CODEWITHSUNDEEP
c#sqlsql-server
J.Doe
J.Doe

Reputation: 137

prevent duplicate entries in database

I have a very simple table from a database at hand which only contains articlename and category as a column. Sometimes I find entries looking like this for whatever reason:

articlename   category
Coca Cola     Softdrink
Coffee        Hotdrinks
Coffee        Hotdrinks

To prevent duplicate entries, I get a List<string> of all articlenames and look if the list contains the articlename I want to insert already. Now my question is, is there any better way of searching for already existing entries based on strings? Do I maybe have to add some kind of id to make the database transactions faster? The table is not really big right now and doesnt have many entries but I am still curious and asking because I am still a novice regarding databases.

Upvotes: 0

Views: 1348

Answers (4)

Hambone
Hambone

Reputation: 16377

I agree with just about everything that has been said -- at a database level, you want a constraint (notably a primary key) that will prevent duplicate entries.

However, there is one additional layer to consider, and that's your application. If your goal is to prevent a duplicate articlename from being entered, there are two approaches:

  1. Using an object model, code it with a state such that if a user enters an articlename that exists, it modifies the existing object rather than creating a new one. This should then trigger any database "save" transactions to do an update instead of an insert. Any ORM should handle this for you. This also gives you the opportunity to have the app warn the user that they are modifying an existing value.

1b. If you want to throw caution to the wind, you can just change you databaset insert to an upsert, where is blindly does the update on a constraint violation.

  1. If your program is doing standard dupe checking, the List will work, but depending on how many items are added you may want to consider a HashSet or a Dictionary<string, ?>, which will be much more efficient. The List requires the program to scan all entries, whereas the HashSet or Dictionary will take it right to the specified entry or know none exists.

If new items are rarely entered, then the List will work fine.

Upvotes: 1

hassaan mustafa
hassaan mustafa

Reputation: 530

If you want to handle at database level

  • you can go with Unique Constraint on Multiple columns
  • or you can create a composite primary key like:
CREATE TABLE TableName (
  Field1 varchar(20),
  Field2  INT,
  PRIMARY KEY (Field1, Field2))

Furthermore if you wish to do this on Code level

  • if you are using Entity framework, you can declare your class/Entity as IEquatable and implement equals functionality in class:
public bool Equals(ClassOrEntity other)
        {
            if (other == null)
                return false;

            if ((this.Field1 == other.Field1)
                && (this.Field2 == other.Field2)
                && (this.Field3 == other.Field3))
                return true;

            return false;
    }

and while inserting values in DB using EF you can check if such value already exists in DB or not:

var ExistingEntries = await context.Entity.Where(p => p.Field1 == Obj.Field1 && p.Field2 == Obj.Field2).FirstOrDefaultAsync();

if(!ExistingEntries.Equals(Obj)
     return error

Upvotes: 1

diego fernandez
diego fernandez

Reputation: 33

Use UNIQUE index or add some type of rule to the table

Upvotes: 2

Himanshu Jain
Himanshu Jain

Reputation: 153

In fact, the better way is to avoid duplicate entries at the first place. You can do that by including a PRIMARY KEY or UNIQUE index on the applicable fields.

You may refer to the following articles:

Upvotes: 1

Related Questions