Changing a column from NULL to NOT NULL consist in making a change on the table by using ALTER TABLE syntax.
There are few steps that you have to follow in order to change a column from null to not null.
- Check the column to see if there is any NULL value, if you find one or more proceed to change it for a default value, (on INT columns it is common to use 0)
- After change is done proceed to make the change on the column by using ALTER TABLE TableName ALTER COLUMN ColumnName Datatype NOT NULL;
- Verify that change worked as expected.
For working on the following examples we are going to use the following dataset.
On this example we are going a create a table and call it users, then fill it with some demo data.
--Creating demo table Create Table users( username varchar(250) ,age int NULL ) --Inserting demo data into the table INSERT INTO users VALUES ('User1',NULL) ,('User2',25) ,('User3',22) ,('User4',NULL) ,('User5',32)
As you can see the created table has a field called ‘age’ that allows NULL values. also we inserted some NULL values into this column.
2. How to check if columns does contain NULL values
The first step is to confirm that change target column does not contain any NULL value, for that you can use ISNULL function into the WHERE clause.
SELECT * FROM users WHERE age IS NULL
Then we need to Update NULL values that we found in the column with default values (something that you pick, It depends on what kind of data do you have)
UPDATE users SET age = 0 WHERE age IS NULL
On this update we maintained the structure of the SELECT statement that we used for discovering the NULL values table had and converted it into an UPDATE statement.
As you can see, all records that contained NULL where updated.
Default value used for updating the table depends on column datatype and your criteria.
3. How to change a column from NULL to NOT NULL using ALTER TABLE
After changing the NULL values that we had on the table, we are going to proceed to change the table structure.
ALTER TABLE users ALTER COLUMN age VARCHAR(250) NOT NULL;
If you didn’t change NULL values that were on the table you are going to receive an error message.