MySQL INSERT IGNORE in this case

Question

In this case, how can I use insert ignore to prevent duplicate rows being inserted?

It all on the userId and elmCol

So:

userId | elmCol
----------------
 1     | 1 //Allow
 1     | 2 //Allow
 1     | 3 //Allow
 1     | 1 //Not allowed if inserted again. I've put it in here just for example)
 2     | 1 //Allow
 2     | 2 //Allow
 2     | 3 //Allow
 2     | 1 //Not allowed if inserted again. I've put it in here just for example)

I'm using MySql and MyIsam type tables. Can I do something like this and use insert ignore?

I tried creating primary keys, but cannot use them on all columns.

Use On Duplicate Key Update instead. Take a look at this link: http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update — Fabian Bigler, May 24 '13 at 11:31
But I have nothing to update! If a duplicate row was inserted, simply don't insert it. That's all I'm trying to do. — Norman, May 24 '13 at 11:33
@Norman from where you get this value..i mean how you inserting in this table one by one or whole as table — Amit Singh, May 24 '13 at 11:36
@Norman: You're right, sorry I wasn't aware these are the only columns in your table. A composite primary key should solve your problem, though. — Fabian Bigler, May 24 '13 at 11:41

score 1 · Answer 1 · answered May 24 '13 at 11:35

Apply unique Index on both the column.

CREATE UNIQUE  INDEX unique_userId_elmCol    
    ON table1 (userId ,elmCol);

OR If you don't want to insert duplicate values in table and rather wants to keep that value in different table.

You can create trigger on table. like this:

DELIMITER $$
CREATE TRIGGER unique_key_ignore BEFORE INSERT ON table1
FOR EACH ROW BEGIN
  DECLARE c INT;
  SELECT COUNT(*) INTO c FROM table1 WHERE userId = NEW.userId and elmCol = NEW.elmCol;
  IF (c > 0) THEN
    insert into table2 (userId ,elmCol) values ( NEW.userId , NEW.elmCol);
  END IF;
END$$

DELIMITER ;

score 1 · Accepted Answer · edited May 23 '17 at 12:11

1

Use a composite primary key: How can I define a composite primary key in SQL?

CREATE TABLE yourTable(
  userId NUMERIC,
  elmCol NUMERIC,
  PRIMARY KEY (userId , elmCol)
);

Once you got a composite primary key, you will not be able to insert duplicates.

By the way, you should not use Unique Index for this case because they can be nullable. Check out this link, why not: Primary key or Unique index?

edited May 23 '17 at 12:11

Community

1
1

answered May 24 '13 at 11:36

Fabian Bigler

10,403
6
47
70

1

Amazing. I was trying the same thing, but using HeidiSQL and it would not allow me to add a secondary PRIMARY KEY. – Norman May 24 '13 at 11:41

MySQL INSERT IGNORE in this case

2 Answers2