unique not null and zero

Question

I am new to SQL, and I have created a unique key in a column. Now I want to alter the column where it will not allow any null or zero values.

I tried doing this

ALTER TABLE sales ALTER COLUMN VerificationCode VARCHAR NOT NULL

However this gives the following error:

The object 'UQ_Sales_VerificationCode' is dependent on column 'VerificationCode'. Msg 4922, Level 16, State 9, Line 74 ALTER TABLE ALTER COLUMN VerificationCode failed because one or more objects access this column.

score 1 · Accepted Answer · answered Sep 09 '15 at 04:23

1

Try first dropping the constraint before you alter the table column:

ALTER TABLE sales DROP CONSTRAINT UQ_Sales_VerificationCode

Then do

ALTER TABLE sales ALTER COLUMN VerificationCode VARCHAR NOT NULL

answered Sep 09 '15 at 04:23

Tim Biegeleisen

502,043
27
286
360

can we do this without dropping the constraint ?? – Juan Sep 09 '15 at 04:44
Can you add the constraint back again after altering the table? Check out [this SO post](http://stackoverflow.com/questions/2253185/how-do-i-drop-a-column-with-object-dependencies-in-sql-server-2008) for more information. – Tim Biegeleisen Sep 09 '15 at 05:01
yes your solution works but i was wondering if we can do this without dropping the constraint – Juan Sep 10 '15 at 01:12
I do not know of a way of doing this. – Tim Biegeleisen Sep 10 '15 at 01:16

unique not null and zero

1 Answers1