What happens when we apply NOT NULL constraint, with ALTER TABLE statement, to a column contains NULL values?

In this case, MySQL will return an error message regarding data truncated for the column. Following is an example of demonstrating it −

Example

Suppose we have a table ‘test2’ which contains a NULL value in column ‘ID’ at 2nd row. Now, if we will try to declare the column ID to NOT NULL then MySQL will return the error as follows −

mysql> Select * from test2;
+------+--------+
| ID   | Name   |
+------+--------+
| 1    | Gaurav |
| NULL | Rahul  |
+------+--------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE TEST2 MODIFY ID INT NOT NULL;
ERROR 1265 (01000): Data truncated for column 'ID' at row 2

Karthikeya Boyini

Published on 16-Feb-2018 11:54:58

• Related Questions & Answers
• How can we apply a NOT NULL constraint to a column of an existing MySQL table?
• What is MySQL NOT NULL constraint and how can we declare a field NOT NULL while creating a table?
• Change a MySQL column to have NOT NULL constraint
• How can we remove NOT NULL constraint from a column of an existing MySQL table?
• Display only NOT NULL values from a column with NULL and NOT NULL records in MySQL
• Alter a table column from VARCHAR to NULL in MySQL
• How to insert NULL keyword as a value in a character type column of MySQL table having NOT NULL constraint?
• How to add not null constraint to existing column in MySQL?
• Check for NULL or NOT NULL values in a column in MySQL
• What MySQL returns when we use DISTINCT clause with the column having multiple NULL values?
• Adding a new NOT NULL column to an existing table with records
• How to add NOT NULL constraint to an already created MySQL column?
• How to add a NOT NULL constraint to a column of a table in a database using JDBC API?
• Which statement, other than ALTER TABLE statement, can be used to apply UNIQUE constraint to the field of an existing MySQL table?
• Perform mathematical calculations in a MySQL table with NULL and NON-NULL values