CODEWITHSUNDEEP
mysqlinsert
ihtus
ihtus

Reputation: 2811

INSERT new row IF NOT EXISTS

Table contents:
+----+---------+-----------+
| id | title   | author    |
+----+---------+-----------+
|  1 | book_1  | author_1  |
|  2 | book_2  | author_2  |
|  3 | book_3  | author_3  |
+----+---------+-----------+

id - is UNIQUE and AUTO_INCREMENTAL

How to prevent INSERT of duplicate row (duplicate only by title and author columns) to be executed?

For example, this should be ignored in case it's executed (because we already have 'book_2','author_2'):

INSERT INTO books (id, title, author) VALUES (NULL,'book_2','author_2');

But these kind of INSERTs to be allowed (because we don't have yet 'book_4','author_4'; 'book_2','author_4')

INSERT INTO books (id, title, author) VALUES (NULL,'book_4','author_4');
INSERT INTO books (id, title, author) VALUES (NULL,'book_2','author_4');

Upvotes: 0

Views: 38

Answers (1)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

You should create table with UNIQUE KEY like: (fiddle1)

CREATE TABLE `books` (
  id INT AUTO_INCREMENT NOT NULL, 
  `title` VARCHAR(60) NOT NULL, 
  `author` VARCHAR(60) NOT NULL,
  UNIQUE KEY `unique_book` (`title`, `author`), 
  PRIMARY KEY(id)
);

If table already exists you can alter it by adding UNIQUE KEY: (fiddle2)

CREATE UNIQUE INDEX `unique_book` ON `books` (`title`, `author`);

Upvotes: 1

Related Questions