PL/SQL INSERT IGNORE

The INSERT IGNORE statement in MySQL is used to insert data into a table while ignoring errors caused by duplicate key constraints. This ensures that the insertion operation continues without interruption even if some records violate uniqueness constraints.

While Oracle’s PL/SQL does not directly support INSERT IGNORE, similar functionality can be implemented using MySQL. In this article, we will learn about PL/SQL INSERT IGNORE with various examples

What is PL/SQL INSERT IGNORE?

In MySQL, INSERT IGNORE allows you to insert data into a table, ignoring any errors that occur due to duplicate key constraints. This is useful for scenarios where you want to avoid inserting duplicate records but don't want to halt execution if duplicates are encountered

Syntax

INSERT IGNORE INTO table_name (column1, column2)

VALUES (value1, value2);

Key Terms:

• table_name: This is the table into which data will be inserted.
  
• column1, column2: The columns into which data will be inserted.
  
• value1, value2: The values to be inserted.
  
• The INSERT IGNORE statement will by pass errors caused by duplicate key constraints, allowing the insertion process to continue without interruption.

PL/SQL INSERT IGNORE Example

In this example, we create two tables Books and Authors. The Books table stores information about books, including their BookID, BookTitle, and AuthorID. The Authors table holds the AuthorID and AuthorName. After creating the tables, we insert records into each.

Books Table:

We start by creating the Books table. This table has three columns: BookID, which uniquely identifies each book; BookTitle, which stores the name of the book; and AuthorID, which links the book to its author in the Authors table.

Query:

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    BookTitle VARCHAR(100),
    AuthorID INT
);

INSERT INTO Books (BookID, BookTitle, AuthorID) VALUES (1, 'The Great Gatsby', 101);
INSERT INTO Books (BookID, BookTitle, AuthorID) VALUES (2, 'To Kill a Mockingbird', 102);
INSERT INTO Books (BookID, BookTitle, AuthorID) VALUES (3, '1984', 103);

Output:

Explanation:

The Books table now contains three rows:

• "The Great Gatsby" linked to AuthorID 101
  
• "To Kill a Mockingbird" linked to AuthorID 102
  
• "1984" linked to AuthorID 103

Authors Table:

Next, we insert three records into the Books table. Each record represents a different book, along with the corresponding AuthorID that links to an author in the Authors table.

Query:

CREATE TABLE Authors (    
AuthorID INT PRIMARY KEY,   
 AuthorName VARCHAR(100)
);

INSERT INTO Authors (AuthorID, AuthorName) VALUES (101, 'F. Scott Fitzgerald');
INSERT INTO Authors (AuthorID, AuthorName) VALUES (102, 'Harper Lee');
INSERT INTO Authors (AuthorID, AuthorName) VALUES (103, 'George Orwell');

Output:

Explanation:

The Authors table now contains three rows:

• AuthorID 101 linked to "F. Scott Fitzgerald"
  
• AuthorID 102 linked to "Harper Lee"
  
• AuthorID 103 linked to "George Orwell"

Example 1: Insert a New Book Ignoring Duplicates

This example demonstrates how to insert a new record into the Books table while ignoring any potential duplicate BookID values. If the BookID already exists in the table, the insertion is ignored, and no error is generated.

Query:

INSERT IGNORE INTO Books (BookID, BookTitle, AuthorID)
VALUES (4, 'Brave New World', 104);

Output:

Explanation:

• If BookID 4 does not exist, it is added to the Books table.
  
• If BookID 4 already exists, no new entry is made, and no error is raised.

Example 2: Insert Multiple Books with Error Ignoring

This statement attempts to insert multiple books. If any BookID already exists (e.g., ID 1), those records will be ignored without causing an error.

Query:

INSERT IGNORE INTO Books (BookID, BookTitle, AuthorID)
VALUES (5, 'Catch-22', 105),
       (1, 'The Catcher in the Rye', 106);

Output:

Explanation:

• Book with BookID 5 is added if it doesn't already exist.
  
• Book with BookID 1 is ignored if it already exists, without causing an error.

Example 3: Insert Author Only if Not Exists

This example shows how to insert a new author into the Authors table, but only if the AuthorID does not already exist. If an author with AuthorID 104 already exists, the insertion will be ignored, and no error will be thrown.

Query:

INSERT IGNORE INTO Authors (AuthorID, AuthorName)
VALUES (104, 'Aldous Huxley');

Output:

Explanation:

• If AuthorID 104 does not already exist in the table, a new row is added with the author name "Aldous Huxley."
  
• If AuthorID 104 is already present, the insertion is ignored, and no changes are made to the table.

Example 4: Insert Books with Different Conditions

This example demonstrates how to insert multiple books into the Books table using the INSERT IGNORE statement. If any of the BookID values already exist, those specific records will be ignored, preventing duplication and avoiding any errors.

Query:

INSERT IGNORE INTO Books (BookID, BookTitle, AuthorID)
VALUES (6, 'The Road', 107),
       (7, 'Neuromancer', 108);

Output:

Explanation:

• If BookID 6 or 7 does not exist in the Books table, the corresponding book records ("The Road" and "Neuromancer") are added successfully.
  
• If BookID 6 or 7 already exists, those specific insertions are ignored, and the table remains unchanged for those IDs.

Conclusion

The INSERT IGNORE statement in MySQL is a practical tool for inserting data while avoiding interruptions caused by duplicate key constraints. Although PL/SQL does not directly support INSERT IGNORE, similar behavior can be implemented in MySQL, ensuring that data insertion processes can handle duplicates gracefully. The examples provided demonstrate how to use INSERT IGNORE effectively to maintain data integrity while avoiding errors.