Normally if we try to insert data that violates a CHECK constraint in MySQL, we get an error, and the whole INSERT operation fails (including any conforming rows).

But it’s possible to change this, so that any conforming rows are inserted, and only the non-conforming rows are skipped.

We can do this by using the IGNORE clause.

Example

Suppose we add the following CHECK constraint to a table called Products:

ALTER TABLE Products 
ADD CHECK (ProductPrice > RecommendedPrice);

This CHECK constraint checks that value in the ProductPrice column is greater than the value in the RecommendedPrice column.

Here’s an example of trying to insert data that violates that CHECK constraint:

INSERT INTO Products 
    ( ProductId, ProductName, ProductPrice, RecommendedPrice )
VALUES 
    ( 1, "Rope", 7, 5 ),
    ( 2, "Wrench", 9, 15 ),
    ( 3, "Saw", 20, 15 );

Result:

ERROR 3819 (HY000): Check constraint 'products_chk_1' is violated.

Here, the second row violates the CHECK constraint, because the ProductPrice is lower than the RecommendedPrice. As expected, we get an error.

Let’s see if any data was inserted:

SELECT * FROM Products;

Result:

Empty set (0.00 sec)

So nothing was inserted.

Now let’s add the IGNORE clause to our INSERT statement in order to skip the violating row:

INSERT IGNORE INTO Products 
    ( ProductId, ProductName, ProductPrice, RecommendedPrice )
VALUES 
    ( 1, "Rope", 7, 5 ),
    ( 2, "Wrench", 9, 15 ),
    ( 3, "Saw", 20, 15 );

Result:

Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

This time the message tells us that the query is OK, that two rows were affected, and a warning was issued.

Let’s check the warning:

SHOW WARNINGS;

Result:

+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 3819 | Check constraint 'products_chk_1' is violated. |
+---------+------+------------------------------------------------+

So the warning tells us the same thing that the error told us.

Let’s check the data:

SELECT * FROM Products;

Result:

+-----------+-------------+--------------+------------------+
| ProductId | ProductName | ProductPrice | RecommendedPrice |
+-----------+-------------+--------------+------------------+
|         1 | Rope        |         7.00 |             5.00 |
|         3 | Saw         |        20.00 |            15.00 |
+-----------+-------------+--------------+------------------+

This time the conforming rows were inserted. So we successfully inserted all conforming rows, while skipping any non-conforming rows.