Just use an ALTER TABLE... MODIFY... query and add NOT NULL into your existing column definition. For example:
ALTER TABLE Person MODIFY P_Id INT(11) NOT NULL;
A word of caution: you need to specify the full column definition again when using a MODIFY query. If your column has, for example, a DEFAULT value, or a column comment, you need to specify it in the MODIFY statement along with the data type and the NOT NULL, or it will be lost. The safest practice to guard against such mishaps is to copy the column definition from the output of a SHOW CREATE TABLE YourTable query, modify it to include the NOT NULL constraint, and paste it into your ALTER TABLE... MODIFY... query.
Just use an ALTER TABLE... MODIFY... query and add NOT NULL into your existing column definition. For example:
ALTER TABLE Person MODIFY P_Id INT(11) NOT NULL;
A word of caution: you need to specify the full column definition again when using a MODIFY query. If your column has, for example, a DEFAULT value, or a column comment, you need to specify it in the MODIFY statement along with the data type and the NOT NULL, or it will be lost. The safest practice to guard against such mishaps is to copy the column definition from the output of a SHOW CREATE TABLE YourTable query, modify it to include the NOT NULL constraint, and paste it into your ALTER TABLE... MODIFY... query.
Try this, you will know the difference between change and modify,
ALTER TABLE table_name CHANGE curr_column_name new_column_name new_column_datatype [constraints]
ALTER TABLE table_name MODIFY column_name new_column_datatype [constraints]
- You can change name and datatype of the particular column using
CHANGE. - You can modify the particular column datatype using
MODIFY. You cannot change the name of the column using this statement.
Hope, I explained well in detail.
What happens when I put a NOT NULL constraint in MySQL while importing a CSV file and the file contains NULL values?
sql server - insert a NOT NULL column to an existing table - Stack Overflow
NOT NULL & DEFAULT Constraints
Need some knowledge on NULL and NOT NULL
Videos
I am importing data from a CSV file to MySQL for the first time. Many tutorials I have seen recommends putting a NOT NULL constraint to all of the columns. I am not certain if certain columns don't have NULL values so I am only putting the constraint where I am certain there are no NULL values. But I want to know what would happen if there are NULL values in a column where I put a NOT NULL constraint? Would the values just display 'NULL' or would there be an error in importing the file?
As an option you can initially create Null-able column, then update your table column with valid not null values and finally ALTER column to set NOT NULL constraint:
ALTER TABLE MY_TABLE ADD STAGE INT NULL
GO
UPDATE MY_TABLE SET <a valid not null values for your column>
GO
ALTER TABLE MY_TABLE ALTER COLUMN STAGE INT NOT NULL
GO
Another option is to specify correct default value for your column:
ALTER TABLE MY_TABLE ADD STAGE INT NOT NULL DEFAULT '0'
UPD: Please note that answer above contains GO which is a must when you run this code on Microsoft SQL server. If you want to perform the same operation on Oracle or MySQL you need to use semicolon ; like that:
ALTER TABLE MY_TABLE ADD STAGE INT NULL;
UPDATE MY_TABLE SET <a valid not null values for your column>;
ALTER TABLE MY_TABLE ALTER COLUMN STAGE INT NOT NULL;
If you aren't allowing the column to be Null you need to provide a default to populate existing rows. e.g.
ALTER TABLE dbo.YourTbl ADD
newcol int NOT NULL CONSTRAINT DF_YourTbl_newcol DEFAULT 0
On Enterprise Edition this is a metadata only change since 2012