Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2009-05-07
11
Medium Priority
?
1,054 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 400 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 1600 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

604 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