Resolved: Difference between MODIFY and CHANGE COLUMN when adding NOT NULL constraint for column

Question

Hello,

I would like to ask how is different:
MODIFY headquarters_phone_number VARCHAR(255) NOT NULL;
query from
CHANGE COLUMN headquarters_phone_number headquarters_phone_number VARCHAR(255) NOT NULL;

It seems to me, they do same the thing.
In the solution for exercise DEFAULT Constraints (see link below) changing between NULL and NOT NULL is like this:
https://learn.365datascience.com/courses/sql/default-constraint
ALTER TABLE companies
MODIFY headquarters_phone_number VARCHAR(255) NULL;
ALTER TABLE companies
CHANGE COLUMN headquarters_phone_number headquarters_phone_number VARCHAR(255) NOT NULL;

I was wondering why not use:
MODIFY headquarters_phone_number VARCHAR(255) NOT NULL;
intead of
CHANGE COLUMN headquarters_phone_number headquarters_phone_number VARCHAR(255) NOT NULL;

After I tried out both, I cannot see difference.
Therefore, can I state, that both approaches does same thing or are there some differences?

Thank you.
Cheers,
Maros J

Answer 1

Hi Maros! Thanks for reaching out. Sometimes the two options can be used as alternatives. In other situations, you can use only one or the other. For instance, with CHANGE you are able to change the name of a column while using MODIFY disallows that. Therefore, they are interchangeable, and if you don't need to change the column name, it is preferably to use MODIFY. Throughout the course, we've used both to show that both can be used and because we did not want to deprive the course code from any of the two options. Hope this helps.
Best,
Martin

Answer 2

I would like to add an important detail here,

While doing the MODIFY COLUMN query there is often an error called:

Cannot change column so and so as used in the foreign key constraint.

Meaning the presence of a foreign key avoids modifying the column.

For it add:

SET foreign_key_checks = 0;

before the query, execute it and then run the ALTER QUERY having Modify column.

It will run without the above error and can later set the Foreign Key check again to 1.

SET foreign_key_checks = 1;

Thanks!

Resolved: Difference between MODIFY and CHANGE COLUMN when adding NOT NULL constraint for column

Submit an answer