2

So I want to insert a new row but after checking, it doesn't already exist.

My table is quite simple;

CREATE TABLE IF NOT EXISTS table
(
    id INT NOT NULL AUTO_INCREMENT,
    pl VARCHAR(255) NOT NULL,
    num1 INT NOT NULL,
    num2 INT NOT NULL, 
    num3 INT NOT NULL,

    PRIMARY KEY (pl)
) ENGINE=InnoDB;

So I changed the values to something simple for convenience.

I tried doing INSERT INTO table(pl, num1, num2, num3) VALUES(val,0,0,0) ON DUPLICATE KEY UPDATE pl=pl; but the above still enters the record cause of the auto increment field.

I also tried doing the same but instead of ON DUPLICATE, INSERT IGNORE INTObut I got the same result.

So is there any way to insert the row only if the primary key doesn't exist and if it does do nothing? (right now a new identical row gets created where the increment is +1

nonerth
  • 549
  • 2
  • 7
  • 19
  • You can add [UNIQUE](https://www.w3schools.com/sql/sql_unique.asp) constraint while creating the table to any of your column or multiple columns. – Amr Aly May 08 '17 at 00:44
  • So you mean preventing the insert by throwing an error? – nonerth May 08 '17 at 00:45
  • You can catch the error. Check this [answer](http://stackoverflow.com/questions/8449540/php-detect-mysql-update-insertion-failure-due-to-violated-unique-constraint) – Amr Aly May 08 '17 at 00:49
  • Yeah I know I catch the error, but I suppose there is a better way of doing this than just throwing and catching the error, you know seems kinda unprofessional. Am just trying to improve efficiency otherwise I could select and then update on empty row. – nonerth May 08 '17 at 00:50
  • You right, However this constraint will guarantee that none of your records will be duplicate. – Amr Aly May 08 '17 at 00:55
  • 1
    Indeed valid point, most probably I will add that in my primary key regardless of the method I end up using on the insert, thanks – nonerth May 08 '17 at 00:57

0 Answers0