How to Add, Modify and Drop Column with Default Value, NOT NULL Constraint – MySQL Example

To add, modify, and drop a column with a default value and NOT NULL constraint in MySQL, you can use the ALTER TABLE statement. 

Here are examples for each operation:

Adding a column with a default value and NOT NULL constraint:

ALTER TABLE table_name
ADD COLUMN column_name datatype DEFAULT default_value NOT NULL;

Replace table_name with the name of your table, column_name with the name of the new column, datatype with the appropriate data type for the column, and default_value with the desired default value. 

 For example, let's say we have a table named "users" and we want to add a column named "age" with the default value of 0:

ALTER TABLE users
ADD COLUMN age INT DEFAULT 0 NOT NULL;

Modifying a column to have a default value and NOT NULL constraint:

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value,
ALTER COLUMN column_name SET NOT NULL;

Replace table_name with the name of your table and column_name with the name of the column you want to modify.

For example, let's say we want to modify the "age" column in the "users" table to have a default value of 18:

ALTER TABLE users
ALTER COLUMN age SET DEFAULT 18,
ALTER COLUMN age SET NOT NULL;

Dropping a column:

ALTER TABLE table_name
DROP COLUMN column_name;

Replace table_name with the name of your table and column_name with the name of the column you want to drop. For example, let's say we want to drop the "age" column from the "users" table:

ALTER TABLE users
DROP COLUMN age;Note that when dropping a column, any data stored in that column will be permanently lost. Therefore, exercise caution when performing this operation and make sure to have a backup of your data if necessary.

Note that when dropping a column, any data stored in that column will be permanently lost. 

Therefore, exercise caution when performing this operation and make sure to have a backup of your data if necessary.