Doubt on unique non clustered Index

Hi All,
I have a table with data:
CREATE TABLE [dbo].[Relation] (
[ID] [uniqueid] IDENTITY (1, 1) NOT NULL ,
[PersonID] [uniqueid] NOT NULL ,
[CurrentID] [uniqueid] NOT NULL ,
[RelativeName] [varchar] (50) NOT NULL ,
)
Here there are duplicate records for a combination of PersonID, CurrentID
and RelativeName.
I need to create a unique non clustered index on PersonID, CurrentID and
RelativeName.
But it throws an error saying duplicate values...
Why I want to create such an index is to stop inserting further duplicates
and the old data is important for me.
Is there any other way i can accomplish this?
Thanks,
PradYou ought to be able to get rid of the duplicates, otherwise your table
lacks integrity because of the redundant data. Why do you say that the old
data is important to you? In what way?
First, you'll have to fix any foreign key references:
UPDATE foo
SET id =
(SELECT MIN(R2.id)
FROM Relation AS R1
, Relation AS R2
WHERE R1.id = foo.id
AND R1.personid = R2.personid
AND R1.currentid = R2.currentid
AND R1.relativename = R2.relativename) ;
Then delete the duplicates:
DELETE FROM Relation
WHERE EXISTS
(SELECT *
FROM Relation AS R
WHERE R.personid = Relation.personid
AND R.currentid = Relation.currentid
AND R.relativename = Relation.relativename
AND R.id < Relation.id) ;
Now you can add the unique constraint.
Hope this helps.
David Portas
SQL Server MVP
--|||Hi
Maybe you should be changing/consolidating the data related that makes this
non-unique. This does not necessarily mean deleting it, for example having a
alternateids table would mean that only one id needs to exist in the "master
"
table.
John
"Pradeep Kutty" wrote:

> Hi All,
> I have a table with data:
> CREATE TABLE [dbo].[Relation] (
> [ID] [uniqueid] IDENTITY (1, 1) NOT NULL ,
> [PersonID] [uniqueid] NOT NULL ,
> [CurrentID] [uniqueid] NOT NULL ,
> [RelativeName] [varchar] (50) NOT NULL ,
> )
> Here there are duplicate records for a combination of PersonID, CurrentID
> and RelativeName.
> I need to create a unique non clustered index on PersonID, CurrentID and
> RelativeName.
> But it throws an error saying duplicate values...
> Why I want to create such an index is to stop inserting further duplicates
> and the old data is important for me.
> Is there any other way i can accomplish this?
> Thanks,
> Prad
>
>|||I prefer this one:
Delete from Relation
Where
[id] not in
(
SELECT MAX([id])
FROM Relation
GROUP BY [personid],[currentid],[relativename])
Regards,
"David Portas" wrote:

> You ought to be able to get rid of the duplicates, otherwise your table
> lacks integrity because of the redundant data. Why do you say that the old
> data is important to you? In what way?
> First, you'll have to fix any foreign key references:
> UPDATE foo
> SET id =
> (SELECT MIN(R2.id)
> FROM Relation AS R1
> , Relation AS R2
> WHERE R1.id = foo.id
> AND R1.personid = R2.personid
> AND R1.currentid = R2.currentid
> AND R1.relativename = R2.relativename) ;
> Then delete the duplicates:
> DELETE FROM Relation
> WHERE EXISTS
> (SELECT *
> FROM Relation AS R
> WHERE R.personid = Relation.personid
> AND R.currentid = Relation.currentid
> AND R.relativename = Relation.relativename
> AND R.id < Relation.id) ;
> Now you can add the unique constraint.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>