How to prevent duplicate data values from appearing in MySQL Database

Question

I currently have an asp.net website. However I have noticed that in my Registration Page, users are able to register multiple times using the same username. As such, I am wondering if there is a way to prevent the users from registering multiple times using the same username.

I do know that i can set one column in my database to have a primary key... But what do i do if i want to have multiple columns to have primary keys?

This is my Insert Code....

    MySqlCommand command = mcon.CreateCommand();
    mcon.Open();
    command.CommandText = "insert into pointofcontact (FirstName, LastName,        EmailAddress, ContactNumber, BackupContactNumber, Address, Gender, Username, Password, Status, ProfilePic) values(?firstname, ?lastname, ?emailaddress, ?contactnumber, ?backupcontactnumber, ?address, ?gender, ?username, ?password, ?status, ?image)";
    command.Parameters.AddWithValue("?firstname", firstname);
    command.Parameters.AddWithValue("?lastname", lastname);
    command.Parameters.AddWithValue("?emailaddress", email);
    command.Parameters.AddWithValue("?contactnumber", mobileNumber);
    command.Parameters.AddWithValue("?backupcontactnumber", backupNumber);
    command.Parameters.AddWithValue("?address", homeAddress);
    command.Parameters.AddWithValue("?gender", gender);
    command.Parameters.AddWithValue("?username", username);
    command.Parameters.AddWithValue("?password", password);
    command.Parameters.AddWithValue("?status", status);
    command.Parameters.AddWithValue("?image", imageName);
    command.ExecuteNonQuery();
    mcon.Close();

    MySqlDataReader reader = null;
    mcon.Open();
    MySqlCommand command2 = mcon.CreateCommand();
    command2.CommandText = "select * from pointofcontact where Username = ?username";
    command2.Parameters.AddWithValue("?username", tbUsername.Text);
    reader = command2.ExecuteReader();
    if (reader != null && reader.HasRows)
    {
        lblValidate.Text = "Username already exists.";
    }

    Response.Redirect("IndexAfterLogin1.aspx");

The data that the user inputs is able to be inserted into my MySQL Database, but the inputted data is able to be repeated for Username and Email fields for different users, and I do not want that to happen.

Possible duplicate of [How do I specify unique constraint for multiple columns in MySQL?](http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql) — Didier Aupest, Aug 12 '16 at 06:11
At least add some constraints to the database, so you code will raise an exception when attempting inserting a duplicate. — Jeroen van Langen, Aug 12 '16 at 06:12

score 0 · Answer 1 · answered Aug 12 '16 at 06:12

0

For that purpose you can add a unique key in table else create a function to check in database before inserting that's it

answered Aug 12 '16 at 06:12

Arun karthi Mani

100
1
3
15

score 0 · Answer 2 · answered Aug 12 '16 at 06:14

0

Make the field username as primary or unique key. It will not allow duplicate entries for that field

answered Aug 12 '16 at 06:14

shaifali Gupta

380
1
4
16

score 0 · Answer 3 · answered Aug 12 '16 at 06:18

Order your code as follows:

try
{
 MySqlDataReader reader = null;
 mcon.Open();
 MySqlCommand command2 = mcon.CreateCommand();
 command2.CommandText = "select * from pointofcontact where Username = ?username";
 command2.Parameters.AddWithValue("?username", tbUsername.Text);
 reader = command2.ExecuteReader();
 if (reader != null && reader.HasRows)
 {
    lblValidate.Text = "Username already exists.";
    **return;**
 }
 else
 {
  MySqlCommand command = mcon.CreateCommand();
  command.CommandText = "insert into pointofcontact (FirstName, LastName,        EmailAddress, ContactNumber, BackupContactNumber, Address, Gender, Username, Password, Status, ProfilePic) values(?firstname, ?lastname, ?emailaddress, ?contactnumber, ?backupcontactnumber, ?address, ?gender, ?username, ?password, ?status, ?image)";
  command.Parameters.AddWithValue("?firstname", firstname);
  command.Parameters.AddWithValue("?lastname", lastname);
  command.Parameters.AddWithValue("?emailaddress", email);
  command.Parameters.AddWithValue("?contactnumber", mobileNumber);
  command.Parameters.AddWithValue("?backupcontactnumber", backupNumber);
  command.Parameters.AddWithValue("?address", homeAddress);
  command.Parameters.AddWithValue("?gender", gender);
  command.Parameters.AddWithValue("?username", username);
  command.Parameters.AddWithValue("?password", password);
  command.Parameters.AddWithValue("?status", status);
  command.Parameters.AddWithValue("?image", imageName);
  command.ExecuteNonQuery();
  }
}
catch
{
  ....
}
finally
{
 mycon.Close();
}

May I ask what do i enter in for the 'catch' portion of the coding? :) — MrStutterz, Aug 12 '16 at 06:32

How to prevent duplicate data values from appearing in MySQL Database

3 Answers3