Alter table modify or change column
while altering table to add null or not null, do we have o use modify or change column, since its way too confusing. could you please explian in an comprehensible way. Thanks.
Hi Muhammad!
Thanks for reaching out.
When you want to add a NULL or NOT NULL constraint to a column in a table, you can use either MODIFY COLUMN or CHANGE COLUMN in your SQL command. It can be a bit confusing, but here’s a simple breakdown:
- MODIFY: Use this when you want to change the column’s constraints or data type but keep the column name the same. For adding NULL or NOT NULL, this is straightforward.
For example, to make a column accept NULL values, you can write something like:
ALTER TABLE your_table_name MODIFY COLUMN column_name data_type NULL;
- CHANGE COLUMN: This is a bit more powerful because it lets you do everything MODIFY does, but you can also change the column name. It’s a bit more to type, since you need to specify the column name twice (once to identify the old column name, and once to set the new name, even if it’s the same). For example:
ALTER TABLE your_table_name CHANGE COLUMN old_column_name new_column_name data_type NULL;
In most cases where you’re just adding or changing a NULL constraint and not renaming the column, MODIFY COLUMN is the simpler choice.
Hope this helps.
Best,
Tsvetelin