1

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?

Jeremy Morren
  • 615
  • 9
  • 23
  • 1
    You have to drop all the indexes and constraints (and perhaps some other dependencies) that reference this column before you run this - and then add them all back once you successfully run your alter statement. – ColdSolstice Jan 19 '18 at 21:12

1 Answers1

3

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>
SouXin
  • 1,565
  • 11
  • 17
  • Just to confirm, this was the correct answer. In order to help anyone else who has this difficulty, I simply used SQL Server's "Drop and Create to - New File" Functionality to ensure that all indexes were dropped and recreated identically. – Jeremy Morren Sep 21 '18 at 16:51