Unique Constraint on existing table with duplicate data

Question

Unique Constraint on existing table with duplicate data

Anju Renjith

Ten Centuries

Points: 1135
More actions
January 29, 2013 at 12:08 am

#394045

Hi,
I have a table called GN_CarParkingAreas
Fields:
1. CarParkId int PK
2. CarParkNo varchar(10) not null
3. DevelopmentID int not null
4. Notes varchar(100) null
There are 10 records in the table.
Now I need to add a unique constraint in the table with UNIQUE(CarParkNo,DevelopmentID)
There are rows in the table violating this.
I need to check this constraint only for the new records and ignore the values existing in the table.
I tried
ALTER TABLE GN_CarParkingAreas
WITH NOCHECK ADD CONSTRAINT UC_CarParkNo UNIQUE (CarParkNo,DevelopmentID)
GO
but falied with error
Msg 1505, Level 16, State 1, Line 2
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.GN_CarParkingAreas' and the index name 'UC_CarParkNo'. The duplicate key value is (1, 40).
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
The statement has been terminated.
How can I do this? Please help.
Thanks In Advance,
Anju Renjith.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

e4d4 SSCertifiable Points: 5777 More actions · Answer 1

Anju Renjith (1/29/2013)
...
I need to check this constraint only for the new records and ignore the values existing in the table.

You can't have part of a table with constraint and part without. Create a new table with constrain for new data and you can eg: create a view that will hide for select that there are two tables.

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 2

It seems you have duplicate data in the columns and you will not able to create the unique index on a column with duplicate rows.

You might consider delete the duplicate data

Suppose,

Pk colmn, col1, col2

1 , 2, 40

2 , 2, 40

So the pk clustered index on pkcolumn is fine and unique cannot be created on col1 and col2..

Hope it helps.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 3

Sorry, i haven't seen that line to ignore the current data ...and create a unique index..sorry that is not possible for the table with that data.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Anju Renjith Ten Centuries Points: 1135 More actions · Answer 4

Anju Renjith

Ten Centuries

Points: 1135

January 29, 2013 at 12:37 am

#1581908

I use SQL server 2008 R2

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 5

If you want to create a index for certain search criteria then you could use filtered index(non clustered) for specific type of data.

may be helpful

here[/url]

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

NOCHECK only applies to foriegn keys and check constraints, not unique constraints. Details are in the Books Online. In order to satisfy the value UNIQUE, it kind of has to be, you know, unique. Filtered indexes is an option, but may cause issues. Why can't you just fix the data? That's the better approach.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Anju Renjith Ten Centuries Points: 1135 More actions · Answer 7

Anju Renjith

Ten Centuries

Points: 1135

January 29, 2013 at 3:46 am

#1581980

Thanx all for ur quick response!!!