Add Unique Constraint problem.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Add Unique Constraint problem.

Author

Topic

hdv212
Posting Yak Master

140 Posts

Posted - 2008-05-16 : 06:53:20

hi
i create a sample table by this code and insert some values to it :

create table test(
c1 int,
c2 int)

insert test select 1,2
insert test select 2,44
insert test select 3,56

now, i want to add new column with unique constraint by this code :

alter table test
add c3 int unique

but the following error has shown me :

Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.test' and index name 'UQ__test__2D27B809'.  The duplicate key value is (<NULL>).
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.

where does my problem and how to solve it ?
thaniks

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-16 : 07:18:55

When you create the new column all 3 existing rows will have NULL as its value, hence this column woun't be unique for each row and the alter table will fail. If you want this to work you can either create c3 as an identity-column like this: alter table test add c3 int identity(1, 1) unique or you must create the column first without the unique constraint, then fill all the rows with unique content and then add the constraint.

--
Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources