Solved

SQL Server 2005 Express adding a unique constraint to an existing table

Posted on 2009-05-07
11
1,032 Views
Last Modified: 2012-05-06
Hey folks,

What's the proper method of adding a unique constraint to an existing table? When I add the new column, and attempt to create a unique key, I receive the following:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.table' and the index name 'Indexname_uq'. The duplicate key value is (<NULL>).

and I understand why I'm getting that, but what's the correct way of handling this situation?  Essentially, I just want to create a unique key/index so that when I'm importing rows of data into a table it'll throw an error if the record already exists.


Thanks!
0
Comment
Question by:jmoriarty
  • 6
  • 4
11 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24332931
I am sure, you have duplicate value exist in your field which you are trying to make unique, you have to maintain uniqueness first or use nocheck
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24332957
look at below script: you can use IGNORE_DUP_KEY so it won't check for existing duplicate value



USE [AdventureWorks]

GO

/****** Object:  Index [uni]    Script Date: 05/08/2009 09:32:00 ******/

CREATE UNIQUE NONCLUSTERED INDEX [uni] ON [dbo].[BIKE] 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 

IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]

Open in new window

0
 

Author Comment

by:jmoriarty
ID: 24332995
Hi RiteshShah,

Thank you for the script; I tried something similar to that, but I get the same error:


The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.table' and the index name 'indexname'. The duplicate key value is (<NULL>)

It's the null values doing it from when I added the new column to the table that I plan to index, etc.

Also, I didn't think you could use NOCHECK with unique indexes? I could be wrong - a bit new to SQL Server.


Thanks!
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24333217
well, you have to make your field unique first. you might have more than on NULL in columns. UNIQUE itself means that you have to have unique value in column, no repeated value allowed. either you remove more than one NULL and duplicate value or don't create UNIQUE index.
0
 

Author Comment

by:jmoriarty
ID: 24333269
Right, I understand that part - but how do you go about that for existing table? for example, I'm trying to add a field with a unique constraint - we'll call it Field2 - to Table1. Table1 has existing data. As soon as I add Field2 to Table1, Field2 automatically gains NULL for existing entries. I then want to create the unique constraint so that all -future- data added to that table will follow the unique constraint requirements. Removing the nulls on existing data isn't necessarily the easiest - the table contains some ~5000 records of legacy data that I can't simply delete.

Does that make sense? I'm probably not explaining it entirely correctly.

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24333289
well you can add Field2 with Identity column and create unique index and once you create unique index, you can probably remove identity, values will be there but no identity so that you can add anything you want....
0
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 100 total points
ID: 24343771
generally how you go about adding a new field to a populated table that you want to make mandatry (NOT NULL) and especially if you are going to put a unique index on it is as follows:
1. add the new column to the table but allowing nulls
2. populate the field with appropriate distinct values for the field
3. alter the table and make the column not null now
4. create the unique index as you were trying to do above
as for populating the values in that field, you have not given any good indication of what this value will be, if it is just an Identity, then if you do as Ritesh sugested and make the column and Identity column then it will popluate itself and you are done.  Otherwise, give some idea of what will make and keep it distinct going forward.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24343976
CGLuttrell,

what I understood from the question is, Author doesn't have data so far so he is making blank column and wanted to create unique index so that no duplicate data come in future, since there is no data, he can't make unique index so he can probably have identity on column so that column could get populated with data rather than blank, when he will enter data in future, it will be handled by unique index, if Author wants, he can probably change/update the data entered in indentity column. more we can clear picture by Author's comment :) let him come once again.
0
 

Author Comment

by:jmoriarty
ID: 24347052
Hey guys,

Thanks for the comments. Ritesh is correct, it's for future data consideration, to correct a problem going forward, and to ignore existing data that's already been processed, etc.

There is an identity column on the table already, (auto incrementing rowid) but in theory, couldn't I just remove the identity on rowid long enough to give each NULL value in the new field a unique #, then set the identity back to the rowid?

If that makes sense.
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 400 total points
ID: 24347116
>>There is an identity column on the table already, (auto incrementing rowid) but in theory, couldn't I just remove the identity on rowid long enough to give each NULL value in the new field a unique #, then set the identity back to the rowid?<<

I am sorry I am still not much clear about what you want to convey. all I can say is, you can add/remove identity anytime. If you want to say you want to remove identity from your existing column and want to give it to another column you want to add, I guess that's fine. no issue with that.
0
 

Author Comment

by:jmoriarty
ID: 24347600
No problem, I know it's a bit tough to understand without seeing the convoluted mess in action.  But you did answer my question in any case, and that did work well.

Thanks for the help!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now