The 365 Data Science team is proud to invite you to our own community forum. A very well built system to support your queries, questions and give the chance to show your knowledge and help others in their path of becoming Data Science specialists.
Ask
Anybody can ask a question
Answer
Anybody can answer
Vote
The best answers are voted up and moderated by our team

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

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

Super Learner
0
Votes
1
Answer

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://365datascience.teachable.com/courses/sql/lectures/5518496
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

1 Answer

365 Team

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

Hello Martin. Thank you for explanation. I will remember it. 🙂

3 months

You are very welcome!

3 months