Search code examples
sql-serveruniquealternotnull

unique not null and zero

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.

Solution

  • 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