SQL Server Change Column to NOT NULL

by

In this SQL Server tutorial, I will explain the SQL Server change column to NOT NULL.

I created an e-commerce website that was running smoothly, but one day, I realized that the column stock could store null values. I needed to fix this issue because these columns must not contain null values.

So, I used the ALTER COLUMN command to change the column from NULL to NOT NULL constraint. In this tutorial, I have explained how to change a column to NOT NULL easily and understandably.

SQL Server Change Column to NOT NULL

When any column in the table is defined as NOT NULL, every row will have a valid value, thus ensuring data integrity and consistency.

The column defined as not null can’t contain null values; if the value is not provided to this kind of column, then errors appear in your terminal.

Below is the syntax for changing the existing column of the table to NOT NULL.

ALTER TABLE table_name
ALTER COLUMN col_name datatype NOT NULL;

Where,

  • ALTER TABLE: It is the command to change the properties of the table.
  • table_name: Name the table containing the column to which you want to apply the NOT NULL constraints.
  • ALTER COLUMN: This is the command that alters the column’s properties.
  • col_name: Name of the column on which you want to enforce the constraint NOT NULL.
  • datatype: It is the data type of the column, such as INT, VARCHAR, TEXT, etc.
  • NOT NULL: This constraint doesn’t allow the column to contain null values.

Let’s see with an example. Suppose you have a ‘Customers’ table in your database with columns customer_id, customer_name and email, as shown below.

Viewing Customer Table Before Change Column to Not Null in SQL Server

Also, check if any of the constraints on the table column using the code below.

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'Customers';
Checking Exsiting Constraints on Table Before Changing Column to Not Null in SQL Server

Look, there is only one constraint, ‘PRIMARY KEY’, in the column customer_id. Now, you can change any column to null; I will choose the column email. I want to ensure an email is contained whenever a user’s details are entered into the ‘Customers’ table.

Remember, if the column is not defined as NOT NULL, then it can contain null values, which means if you don’t provide customer email, you will still be able to insert data into the table, but you don’t want that, so use the code below to change the column to NOT NULL.

ALTER TABLE Customers
ALTER COLUMN email NVARCHAR(255) NOT NULL;
SQL Server Change Column to Not Null

Look, the above command was successfully executed. That means the column ’email’ was changed to NOT NULL, or the constraint NOT NULL was applied.

Now, inserting the data into a column without providing the email shows an error. For example, try to insert the following records.

INSERT INTO Customers (customer_id, customer_name) VALUES
(1, 'Joshua');
SQL Server Change Column to Not Null Error

When you try to insert null values in the email column, it shows the error ‘Cannot insert the value NULL into column ’email”.

This is how you can change a column from NULL to NOT NULL.

Conclusion

In this SQL Server tutorial, you learned about SQL Server changing the column to NOT NULL using the ALTER COLUMN command, which allows you to define the constraint NOT NULL on it.

You may like to read;