Solved

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

Posted on 2009-05-07
11
1,038 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

803 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