Why both names are inserted if the length of one string is more than 5?

[Dmitry_Polovinkin]

Couldn’t get it, and found this accepted answer in one SO question

MySQL VARCHAR size limit

which says

If you set a column to be varchar(15) the maximum bytes allowed is 15. Thus you can’t pass it more than 15 characters without modifying the column to support more than 15.

So how exactly the second name is going to be inserted? IGNORE statements only ignore errors in cases:

• When we will try to insert a duplicate key where the column of a table has a PRIMARY or UNIQUE KEY constraint.
• When we will try to add a NULL value where the column of a table has a NOT NULL constraint.
• When we will try to insert a record to a partitioned table where the entered values do not match the format of listed partitions.

The question here seems to be not applicable to one of them, maybe the 3rd point is our case, not sure - but then it should just ignore the error, not ignore the varchar limitation, or what else?

Thanks!

---

Course: https://www.educative.io/collection/5352985413550080/6335716377231360
Lesson: https://www.educative.io/collection/page/5352985413550080/6335716377231360/5238030269939712

[Rozina_Asghar]

Hey, @Dmitry_Polovinkin!

I hope you’re doing great. The question given in the quiz is:

Consider the following table:

CREATE TABLE Name (LastName VARCHAR(5));

What will happen when the following statements are executed?

INSERT INTO Name VALUES('Smith');
INSERT IGNORE INTO Name VALUES('Edward');

There are two answers given, one of which is correct. The following are the options:
A) Both names are inserted successfully.

B) Error: Data too long for column LastName.

Let’s understand the use of INSERT IGNORE first. We use it for performing bulk operations while inserting records. The points that you shared are correct. The INSERT IGNORE statement also truncates the extra letters from the value being inserted if it exceeds the specified range in VARCHAR(). Following is the output of the query in the question:

You will see the following if you list the warnings:

This shows that the records have been inserted successfully, and the error has been changed to a warning which made it possible for the record to be inserted.

I hope it helps. Happy learning!