Differences between CHANGE COLUMN, ALTER COLUMN, and MODIFY
I am confused about when to use these keywords, how they work, and the differences between them. I have seen one answer from https://learn.365datascience.com/question/alter-column-vs-change-column/, but the answer mentions something different from what was done in the video for DEFAULT Constraint. Can you please provide an explanation for the use cases of these keywords? (It would be great if a guideline about this were included in the course materials)
Hi Abdur!
Thanks for reaching out.
The main difference between MODIFY and CHANGE is that when using CHANGE you can also rename the column you are referring to. Other than that both statements serve the same purpose and can be used interchangeably.
CHANGE COLUMN If you have already created your MySQL database, and decide after the fact that one of your columns is named incorrectly, you don't need to remove it and make a replacement, you can simply rename it using CHANGE COLUMN.
ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL FIRST;
MODIFY COLUMN This command does everything CHANGE COLUMN can, but without renaming the column.You can use the modify SQL command if you need to resize a column in MySQL. By doing this you can allow more or less characters than before. You can't rename a column using MODIFY.
ALTER TABLE MyTable MODIFY COLUMN foo VARCHAR(32) NOT NULL AFTER baz;
Note: ALTER TABLE is used for altering a table means to change column name, size, drop column. CHANGE COLUMN and MODIFY COLUMN commands cannot be used without the help of ALTER TABLE command. So, you need to use both with COLUMN. It is supposed that some versions may allow skipping it, but we have to stick to the good practices.
Hope this helps.
Best,
Tsvetelin
Hi Tsvetelin,
What do you mean by FIRST & AFTER keywords in the SQL statements?
Thanks,
Ayman
