Changing a column from NULL to NOT NULL in SQL Server

Question

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?

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
– ColdSolstice, Commented Jan 19, 2018 at 21:12

SouXin · Accepted Answer · 2018-01-19 21:19:48Z

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>

edited Jan 19, 2018 at 21:19

answered Jan 19, 2018 at 21:12

SouXin

1,57411 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeremy Morren Over a year ago

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.

Collectives™ on Stack Overflow

Changing a column from NULL to NOT NULL in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related