prevent duplicate entries in database-CodePudding

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.

CodePudding user response：

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:

CodePudding user response：

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

CodePudding user response：

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