How to I avoid duplicate insertion of data in database?

Karan Thakkar

7y939Databases & DBA

Example

Employe Table

Id Primary key

Name varchar,

Phone int,

Department,

Should I give unique key to avoid duplicate entry?

Like on phone number

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah7y ago
Phone Number????
I lots of application a phone number is been used by 2 or 3 people for example my family Dr has my number under my file and my wifes,and i am sure they have the same number for my 10 year old child

placing a unique key on multiple fileds may not be bad is the number of fields are less than 7 - 10 fields (depends on the data type and the amounf of Upsert and etc.... on that table)

But what i would do is
--- do profiling and find the most common feild that playes a unqie role (its called business key, or Natural Key NK), note that some NK keys are more than 10 fields depending on the business.
--- depending on the results of the profiling i would creat a Unique key on those fields or creat a checksum field of MD5 on all of the NK feilds and check the uniqueness of the records by the checksum MD5

Good luck
+1
Kalyani Shevale7y ago
when u have inserted this record that time u can check this mobile number already exist or not.if this mobile number is already Exist that time u can display" error message"

likewise
//u can use one Query for this mobile is already exist on not checking.

if(check mobile is Exist )
{
//this mobile number already exists.
}
else
{
  //data inserted.
}
+1

Puneet Kankar7y ago

Set unique key on mobile number


try {
 
  } catch(Exception ex)
 {
  if(ex.GetBaseException().GetType() == typeof(SqlException))
 { //Violation of primary key/Unique constraint can be handled here. Also you may 
//check if Exception Message contains the constraint Name 
 } }

and also check at the time of insertion data like

if not exists(select mobilenumber from tablename where mobilenumber=@mobilenumber)

begin

// insert code here

end

Salman Beg7y ago
Hi.
Code will throw exception because as you set Unique Key in Phone number and you are trying to insert duplicate record in it. So, first you check whether phone number is exists or not. If exist then don't insert otherwise insert.
Thanks.
+1
Karan Thakkar7y ago
code is throwing exception for duplicate record cannot be inserted.
+1
Gajendra Jangid7y ago
You Can Set Unique Key on phone number then it will not accept duplicate record
or you can check record when you insert data that phone already exists or not
+2