MYSQL Duplicate rows

Question

I am trying to insert values into a table in MYSQL, the table has a column which should be unique,so that column always will have different values.

I tried putting UNIQUE for the coloumn but it did not work, Also tried putting that column as PRIMARY KEY and insert IGNORE INTO command it did not work (http://www.tutorialspoint.com/mysql/mysql-handling-duplicates.htm)

My intention is to put phonenumber column unique so every value in this column is different. if the newly inserting value is not unique it should skip wihout giving error.

My code to Create table:

  public function create_member_table($table)
 {
$this->sql ="CREATE TABLE IF NOT EXISTS $table ( id BIGINT NOT NULL AUTO_INCREMENT,
                                    username VARCHAR(50) NOT NULL,
                                    phonenumber VARCHAR(20) NOT NULL,
                                    country VARCHAR(2) NOT NULL,
                                    profession VARCHAR(5000) NOT NULL,
                                    profilepic VARCHAR(5000) NOT NULL,
                                    smskey VARCHAR(100) NOT NULL,
                                    status INT NOT NULL,
                                    reg_date_time DATETIME NOT NULL,
                                    UNIQUE (id,phonenumber))
                                    PARTITION BY HASH(id)
                                    PARTITIONS 1023;";
$this->tb = mysqli_query($this->ret,$this->sql);
if(!$this->tb){
  echo "Table not created<br>";

}
else{
  echo "Table created<br>";

}

Insert table:

  public function table_member_insert($table,$phonenumber="",$username="",$country="",$profession="",$profilepic="0",$smskey="",$status="") {

   $this->sql = "INSERT INTO $table
                  (username,phonenumber,country,profession,profilepic,smskey,status,reg_date_time)
                  VALUES
                  ('$username','$phonenumber','$country','$profession','$profilepic','$smskey','$status',now());";

                  $this->tb = mysqli_query($this->ret,$this->sql);
                  if(!$this->tb){
                    echo "values not inserted<br>";

                  }
                  else{
                    echo "values inserted<br>";
                  } }

Isn't there an answer for your question? see stackoverflow.com/questions/1361340/… — mamosek
– mamosek, Commented Jun 28, 2016 at 11:45

Shadow · Accepted Answer · 2016-06-28 11:43:02Z

3

The problem is that you defined the combination of id and phonenumber fields as unique. Since your id field is defined as auto_increment, it will be unique on its own, therefore any combination with phonenumber field will also be unique.

You need to define the phonenumber field alone as unique. After that you can use insert ignore to insert a new record with an existing phone number without raisin an error. However, pls note that in case of a match, the unique index will prevent the entire record from being inserted.

answered Jun 28, 2016 at 11:43

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Cam Over a year ago

Shadow is correct -- you are defining a composite unique index. In addition, to change from your composite to UNIQUE(phonenumber), you'll have to remove your user-defined PARTITION since partitions are organized by unique indices.

Naroju Over a year ago

I removed id and tried. table not at all getting created. Do I have to remove PARTITIONS too?

Shadow Over a year ago

Check the error message MySQL provided you with and act on it. Pls do not ask us to guess error messages when you are readily provided with them. Pls also read @Cam 's comment as well.

Collectives™ on Stack Overflow

MYSQL Duplicate rows

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related