MySQL "Invalid use of NULL value" workaround

Source: Internet
Author: User

1. Description of the problem

Use database query statement "ALTER TABLE information modify ' number ' varchar (255) NOT NULL ' because you want to change the" number "column in the" Information "table to non-null You can change the other columns, but only in the numbered column, the error message is Invalid use of NULL value.

It is strange that the other columns use this command to find out why only numbered columns cannot be checked.

2. Cause of Error

This error can occur because the ' number ' column of the existing data is NULL, and the setting for not null conflicts. (The reason for the search).

I think it's because the table structure of the column has been set in the beginning of the new data table, and the subsequent changes will be in conflict with the previous settings (as to why this change cannot be changed, and I don't understand it) because I set the structure property of the numbering column before I created the table.

Because it cannot be set to non-null, you can succeed by creating a new column and setting not null.

3. Workaround

1) Add a new column to set the structure properties of the column.

ALTER TABLE Information add column ' yyy ' varchar (255) not NULL first; -Newly added columns yyy to the first column.

2) Copy the column contents of the error to the new column and delete the error column

Update information set yyy= ' number '; ---Copy all values of the ' number ' column to the YYY column.

ALTER TABLE information drop ' number '; ---Delete the faulted column.

3) Modify the column name to which the new column name is faulted

ALTER TABLE information change ' yyy ' number ' varchar (255) not null;

MySQL "Invalid use of NULL value" workaround

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.