How to Alter a Column from Null to Not Null in SQL Server?
To alter a column from NULL to NOT NULL in SQL Server, you can use the ALTER TABLE statement with the MODIFY keyword. There are two methods to do it:
Method 1: Using ALTER TABLE statement with MODIFY keyword
ALTER TABLE table_name
ALTER COLUMN column_name data_type NOT NULL;
Suppose we have a table named "employees" with a column named "salary" which allows NULL values. We want to alter the column to not allow NULL values.
ALTER TABLE employees
ALTER COLUMN salary INT NOT NULL;
This will modify the "salary" column to not allow NULL values.
Method 2: Using SQL Server Management Studio (SSMS)
- Open SSMS and connect to the SQL Server instance.
- Expand the Databases folder and select the database containing the table you want to modify.
- Expand the Tables folder and select the table you want to modify.
- Right-click on the table and select "Design".
- In the table designer, select the column you want to modify.
- In the "Column Properties" section, change the "Allow Nulls" property to "No".
- Save the changes by clicking the "Save" button or pressing Ctrl + S.
Note: Before altering a column from NULL to NOT NULL, make sure that there are no NULL values in the column. If there are NULL values, you will need to either update them to non-NULL values or delete them before altering the column.