CODEWITHSUNDEEP
mysqlsql
Norman
Norman

Reputation: 6365

MySQL INSERT IGNORE in this case

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.

Upvotes: 1

Views: 321

Answers (2)

Fabian Bigler
Fabian Bigler

Reputation: 10915

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?

Upvotes: 1

Pritesh Tayade
Pritesh Tayade

Reputation: 630

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 ;

Upvotes: 1

Related Questions