jmoriarty
asked on
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!
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!
RiteshShah
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
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]
ASKER
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!
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!
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.
ASKER
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.
Does that make sense? I'm probably not explaining it entirely correctly.
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....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Thanks for the help!