CODEWITHSUNDEEP
sqlsql-serverunique
Rafał Praczyk
Rafał Praczyk

Reputation: 441

How to add not null unique column to existing table

Is there any way to simply add not null unique column to existing table. Something like default = 1++ ? Or simply add unique column? I tried to add column and then put unique contrain but MS SQL says that: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name (...) The duplicate key value is ( < NULL > ).

Any way to simply add column to existing working table with unique constrain? Should MS SQL really think that null IS a value?

Upvotes: 1

Views: 3999

Answers (3)

HLGEM
HLGEM

Reputation: 96552

If you want an autonumber, you can add an identity.

If you need to populate the values yourself, you add the column allowing nulls, update the values, check to make sure they are unique and that there are no nulls, then add the unique constraint and trhe not null property. It is best to do this during a maintenance window when no one else would be changing data in that table.

Upvotes: 0

Alsin
Alsin

Reputation: 1618

  1. Add not null column with some default.
  2. Update column to be a sequential integers (see row_number() function)
  3. Add UNIQUE constraint or UNIQUE index over new column

You can add IDENTITY column to a table (but from question it is not clear if you need it or not).

Upvotes: 1

knkarthick24
knkarthick24

Reputation: 3216

IDENTITY is all you need: 

ALTER TABLE TestTable
ADD ID INT IDENTITY(1, 1)

Upvotes: 1

Related Questions