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 http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql — mamosek, Jun 28 '16 at 11:45

score 3 · Answer 1 · answered Jun 28 '16 at 11:43

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 '16 at 11:43

Shadow

30,859
10
44
56

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. – Cam Jun 28 '16 at 11:51
I removed `id` and tried. table not at all getting created. Do I have to remove `PARTITIONS` too? – Naroju Jun 28 '16 at 11:58
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. – Shadow Jun 28 '16 at 12:00

MYSQL Duplicate rows

1 Answers1