1

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?

Rafał Praczyk
  • 431
  • 7
  • 16
  • 1
    SQL Server violates the ANSI standard in that in a standard unique constraint it only allows one NULL value (it considers two NULLs equal in this regard, regardless of ANSI_NULLS settings). To get the ANSI behavior you create a filtered unique index instead, which only requires that *all non-NULL rows* are unique. – Aaron Bertrand Jan 20 '15 at 18:20

3 Answers3

1

IDENTITY is all you need: 

ALTER TABLE TestTable
ADD ID INT IDENTITY(1, 1)
knkarthick24
  • 3,106
  • 15
  • 21
1
  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).

Alsin
  • 1,514
  • 1
  • 14
  • 18
  • what if i have thousands of record existing data in into my table how do i make it sequential number or any unique number from default value which initially will set as suggested by you – Gaurav Joshi Feb 18 '20 at 20:58
  • @GauravJoshi, on the first step you won't get sequential numbers, it is just to be able to add a not-null column tho the table. You can set default to be zero. On step 2 you update zeros to sequential numbers. – Alsin Feb 19 '20 at 11:36
0

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.

HLGEM
  • 94,695
  • 15
  • 113
  • 186