Unique Constraint on existing table with duplicate data

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Unique Constraint on existing table with...

Unique Constraint on existing table with duplicate data

Rate Topic

Display Mode

Topic Options

Author

Message

Anju Renjith

Posted Tuesday, January 29, 2013 12:08 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:43 AM
Points: 226, Visits: 57

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.

Post #1412775

e4d4

Posted Tuesday, January 29, 2013 12:23 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 151, Visits: 1,035

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.

Post #1412777

@SQLFRNDZ

Posted Tuesday, January 29, 2013 12:23 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 12:03 PM
Points: 412, Visits: 862

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.

--SQLFRNDZ

Post #1412778

@SQLFRNDZ

Posted Tuesday, January 29, 2013 12:26 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 12:03 PM
Points: 412, Visits: 862

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.

--SQLFRNDZ

Post #1412779

Anju Renjith

Posted Tuesday, January 29, 2013 12:37 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:43 AM
Points: 226, Visits: 57

I use SQL server 2008 R2

Post #1412782

@SQLFRNDZ

Posted Tuesday, January 29, 2013 12:47 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 12:03 PM
Points: 412, Visits: 862

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

--SQLFRNDZ

Post #1412786

Grant Fritchey

Posted Tuesday, January 29, 2013 3:38 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:55 AM
Points: 13,381, Visits: 25,165

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1412864

Anju Renjith

Posted Tuesday, January 29, 2013 3:46 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:43 AM
Points: 226, Visits: 57

Thanx all for ur quick response!!!

Post #1412866

« Prev Topic | Next Topic »

Permissions