Adding a new NOT NULL column to an existing Table with data

Question

I want to add a new NOT NULL column to an existing table which has data in MySQL 5.7. I have seen this question and I am using the solution suggested there.

I am adding the new column as NULL, populate data for the new column and then change the column from NOT NULL to NULL.

-- 1. add new column as null
ALTER TABLE `mytable` ADD COLUMN newCol BIT NULL AFTER curCol;

-- 2. populate default data for new column
SET sql_safe_updates = 0;
UPDATE `mytable` SET newCol = 0;
SET sql_safe_updates = 1;

-- 3. change the column to NOT NULL
ALTER TABLE `mytable` ALTER COLUMN newCol BIT NOT NULL;

But I am getting the following error on the last command:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BIT NOT NULL:' at line 1

Answer 1

This piece of SQL is not valid in MySQL:

ALTER TABLE `mytable` ALTER COLUMN newCol BIT NOT NULL;

Instead, consider :

ALTER TABLE `mytable` MODIFY newCol BIT NOT NULL;

Reference : MySQL ALTER TABLE syntax

Answer 2

You can do this in three steps:

1. Add your new column (initially, let it have NULL values)

   ALTER TABLE my_table ADD COLUMN new_col datatype NULL AFTER cur_col;
   

2. Update the table so that there are no NULL in our new column

   UPDATE my_table SET new_col = 0 WHERE new_col IS NULL;
   

3. Modify our new column to NOT NULL

   ALTER TABLE my_table MODIFY COLUMN new_col datatype NOT NULL;
   

Reference: StackOverflow- Altering a column: null to not null