How to prevent Duplicate records from my table Insert ignore does not work here

Question

mysql> select * from emp;

    +-----+---------+------+------+------+
    | eno | ename   | dno  | mgr  | sal  |
    +-----+---------+------+------+------+
    |   1 | rama    |    1 | NULL | 2000 |
    |   2 | kri     |    1 |    1 | 3000 |
    |   4 | kri     |    1 |    2 | 3000 |
    |   5 | bu      |    1 |    2 | 2000 |
    |   6 | bu      |    1 |    1 | 2500 |
    |   7 | raa     |    2 | NULL | 2500 |
    |   8 | rrr     |    2 |    7 | 2500 |
    |   9 | sita    |    2 |    7 | 1500 |
    |  10 | dlksdgj |    2 |    2 | 2000 |
    |  11 | dlksdgj |    2 |    2 | 2000 |
    |  12 | dlksdgj |    2 |    2 | 2000 |
    |  13 | dlksdgj |    2 |    2 | 2000 |
    |  14 | dlksdgj |    2 |    2 | 2000 |
    +-----+---------+------+------+------+

Here is my table. I want to eliminate or prevent insertion of the duplicate records, as the eno field is auto increment total row never be duplicate, but the records are duplicates. How can I prevent inserting those duplicate records?

I tried using INSERT IGNORE AND ON DUPLICATE KEY UPDATE (I think I have not used them properly).

The way I used them is,

mysql> insert into emp(ename,dno,mgr,sal) values('dlksdgj',2,2,2000);
Query OK, 1 row affected (0.03 sec)

mysql> insert ignore into emp(ename,dno,mgr,sal) values('dlksdgj',2,2,2000);
Query OK, 1 row affected (0.03 sec)

mysql> insert into emp(ename,dno,mgr,sal) values('dlksdgj',2,2,2000) ON DUPLICATE KEY UPDATE eno=eno;
Query OK, 1 row affected (0.03 sec)
mysql> insert into emp(ename,dno,mgr,sal) values('dlksdgj',2,2,2000) ON DUPLICATE KEY UPDATE eno=eno;
Query OK, 1 row affected (0.04 sec

mysql> desc emp;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| eno   | int(11)     | NO   | PRI | NULL    | auto_increment |
| ename | varchar(50) | YES  |     | NULL    |                |
| dno   | int(11)     | YES  |     | NULL    |                |
| mgr   | int(11)     | YES  | MUL | NULL    |                |
| sal   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

Answer 1

alter the table by adding UNIQUE constraint

ALTER TABLE employee ADD CONSTRAINT emp_unique UNIQUE (ename,dno,mgr,sal)

but you can do this if the table employee is empty.

or if records existed, try adding IGNORE

ALTER IGNORE TABLE employee ADD CONSTRAINT emp_unique UNIQUE (ename,dno,mgr,sal)

UPDATE 1

Something went wrong, I guess. You only need to add unique constraint on column ename since eno will always be unique due to AUTO_INCREMENT.

In order to add unique constraint, you need to do some cleanups on your table.

The queries below delete some duplicate records, and alters table by adding unique constraint on column ename.

DELETE a
FROM Employee a
     LEFT JOIN
     (
        SELECT ename, MIN(eno) minEno
        FROM Employee
        GROUP BY ename
     ) b ON a.eno = b.minEno
WHERE b.minEno IS NULL;

ALTER TABLE employee ADD CONSTRAINT emp_unique UNIQUE (ename);

Here's a full demonstration

• SQLFiddle Demo

Answer 2

Create a UNIQUE CONSTRAINT on which you think the duplicacy exist .

like

ALTER TABLE MYTABLE ADD CONSTRAINT constraint1 UNIQUE(column1, column2, column3)

Answer 3

This will work regardless of whether you clean up your table first (i.e. you can stop inserting duplicates immediately and clean up on separate schedule) and without having to add any unique constraints or altering table in any other way:

INSERT INTO
    emp (ename, dno, mgr, sal)
SELECT
    e.ename, 2, 2, 2000
FROM
    (SELECT 'dlksdgj' AS ename) e
    LEFT JOIN emp ON e.ename = emp.ename
WHERE
    emp.ename IS NULL

The above query assumes you want to use ename as a "unique" field, but in the same way you could define any other fields or their combinations as unique for the purposes of this INSERT.

It works because it's an INSERT ... SELECT format where the SELECT part only produces a row (i.e. something to insert) if its left joined emp does not already have that value. Naturally, if you wanted to change which field(s) defined this "uniqueness" you would modify the SELECT and the LEFT JOIN accordingly.