Search code examples
sql-serversql-server-2014-express

Changing a column from NULL to NOT NULL in SQL Server

I am trying to change a column from NULL to NOT NULL in SQL Server 2014. This is my code:

ALTER TABLE purchase_order_line ALTER COLUMN pol_sl_id INT NOT NULL

However, I am prevented from doing so by the following error message:

Msg 5074, Level 16, State 1, Line 1
The index 'idx_pol_33' is dependent on column 'pol_sl_id'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN pol_sl_id failed because one or more objects access this column.

I can confirm that there are no NULL values in this column. Can anyone help me?

Solution

  • Your errors are straightforward. You have to drop and recreate indexes and statistics.

    Like: 

    DROP STATISTICS table.statistics_name | view.statistics_name [ ,...n ]
DROP INDEX <table_name>.<index_name>