Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1063
  • Last Modified:

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

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
jmoriarty
Asked:
jmoriarty
  • 6
  • 4
2 Solutions
 
RiteshShahCommented:
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
 
RiteshShahCommented:
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
 
jmoriartyAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
RiteshShahCommented:
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
 
jmoriartyAuthor Commented:
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
 
RiteshShahCommented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
RiteshShahCommented:
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
 
jmoriartyAuthor Commented:
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
 
RiteshShahCommented:
>>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
 
jmoriartyAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now