Best Way to Avoid Duplicate Entry into MySQL Database

Best way to avoid duplicate entry into mysql database

First step would be to set a unique key on the table:

ALTER TABLE thetable ADD UNIQUE INDEX(pageid, name);

Then you have to decide what you want to do when there's a duplicate. Should you:

1. ignore it?

   INSERT IGNORE INTO thetable (pageid, name) VALUES (1, "foo"), (1, "foo");
   

2. Overwrite the previously entered record?

   INSERT INTO thetable (pageid, name, somefield)
   VALUES (1, "foo", "first")
   ON DUPLICATE KEY UPDATE (somefield = 'first')
   
   INSERT INTO thetable (pageid, name, somefield)
   VALUES (1, "foo", "second")
   ON DUPLICATE KEY UPDATE (somefield = 'second')
   

3. Update some counter?

   INSERT INTO thetable (pageid, name)
   VALUES (1, "foo"), (1, "foo")
   ON DUPLICATE KEY UPDATE (pagecount = pagecount + 1)
   

MYSQL: How to avoid inserting duplicate records?

INSERT INTO TABLE_2
  (id, name)
SELECT t1.id,
       t1.name
  FROM TABLE_1 t1
 WHERE NOT EXISTS(SELECT id FROM TABLE_2 t2 WHERE t2.id = t1.id)

prevent duplicate entries to database

Sorry to say this is the wrong approach.

Databases have a built in system to prevent data being duplicated. That's through primary keys or unique key constraints. In your case, you have already created a primary key. So there is absolutely no need for you to do that SELECT COUNT(*) query.

Instead, just directly insert into the table and catch the integrity error when the pcode already exists.

Try
    cmd = New MySqlCommand("Insert into personnel values('" & pcode.Text & "','" & lname.Text & "','" & fname.Text & "','" & office.Text & "','" & designation.Text & "')")

    i = cmd.ExecuteNonQuery


    If pcode.Text <> "" Then
    ElseIf i > 0 Then
        MsgBox("Save Successfully!", MessageBoxIcon.Information, "Success")
        mrClean()
        ListView1.Tag = ""
        Call objLocker(False)
        Call LVWloader()
        Call calldaw()
    Else
        MsgBox("Save Failed!", MessageBoxIcon.Error, "Error!")
    End If
Catch ex As MySqlException
    MsgBox("Personnel ID Already Exist!", MessageBoxIcon.Error, "Error!")
End Try

Please also refer to the MySQL Manual Page PRIMARY KEY and UNIQUE Index Constraints

Prevent duplicate data PHP Mysql

You can add a UNIQUE constraint to the email field. This will be done by running the following query:

ALTER TABLE `event` ADD UNIQUE (email);

After that, when you want to insert a new row with an email that already exists in your table, you'll get an error like this: #1062 - Duplicate entry ...

You can catch that exception and react on it in your PHP code like this:

<?php
// perform INSERT query...
if (mysqli_errno($link) == 1062) {
    print 'An error occured';
}

How to avoid duplicate entries in a MySQL database without throwing an error

You can utilize the INSERT IGNORE syntax to suppress this type of error.

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

In your case, the query would become:

INSERT IGNORE INTO `database` (title, introduction) VALUES (%s, %s)

Prevent duplicate values in database - mysql

You want a unique index on event_id and day:

create unique index idx_recurrence_event_day on recurrence(event_id, day)

This will prevent the duplication that you do not want.

If you already have duplicates there are various ways you can get rid of them. However, it would be easier if you had a unique id for each row, which one reason why an auto-incremented primary key is useful in all tables.

One way to get rid of them even without a unique id is to use:

alter ignore table recurrence add unique (event_id, day);

Personally, I don't like this method because adding an index shouldn't delete rows, but this is a valid MySQL extension.

---

Related Topics