Create Unique Constraint without existing data validation

riazpk
riazpk used Ask the Experts™
on
Dear Gurus,

I have a non-unique index on t(c1,c2). There combination of c1,c2 is not unique and there are some duplicate values.

I want to create a unique constraint on t(c1,c2) which will:

- NOT Create a new UNIQUE index but will use the existing non-unique index
- NOT validate the existing data in the table (like in Oracle we have NOVALIDATE clause).

How i can do that? I am using SQL Server 2005.

regards,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
CREATE UNIQUE NONCLUSTERED INDEX [IX_DupIndex_C1C2]
ON [t]
(
[c1] ASC,
[c2] ASC
) WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY]
GO

Author

Commented:
Ritesh,

I get below error:

Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name <Object Name> and the index name <Index Name>. The duplicate key value is <Values Pair>.
The statement has been terminated.


Follow below Steps :
1. Create a new table.
2. CREATE UNIQUE NONCLUSTERED INDEX [IX_DupIndex_C1C2]
ON [t]
(
[c1] ASC,
[c2] ASC
) WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY]
GO

3. Insert data from old table to new table.
4. exec sp_rename 'oldTableName' , 'newTableName'
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

first you have to remove duplicate data than you can create UNIQUE INDEX

Author

Commented:
@Ritesh,

Please re-read my question if you don't mind. I don't want to do that.
concept of UNIQUE key is, it doesn't allow you to have same value in the unique column, if you already have duplicate value in your column than how come it violate the base principle of UNIQUE key and let you create index.

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/36827;pt=36742/*

http://blogs.msdn.com/b/craigfr/archive/2008/01/30/maintaining-unique-indexes-with-ignore-dup-key.aspx

Author

Commented:
We are talking about UNIQUE Constraint; not about UNIQUE INDEX. In Oracle, we can use non-unique index to enforce/create/define unique constraint. And we have an option where we can say we don't want to validate the existing data; only the newly inserted/updated rows. So i am looking for SQL Server Equivalent for this,

Author

Commented:
Here is demonstration in Oracle:

SQL> create table t( a number);

Table created.

SQL> insert into t values (1);

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM t;

         A
----------
         1
         1

SQL> create index t_idx1 on t(a);

Index created.

SQL> alter table t add constraint t_uk unique(a) novalidate;

Table altered.

SQL> SELECT * FROM t;

         A
----------
         1
         1

SQL> insert into t values(1);
insert into t values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (RIAZ.T_UK) violated

Open in new window

SQL-Server uses a unique index to enforce a UNIQUE constraint. A unique index does not allow duplicates, under no circumstances.

The following text is also part of the BOL article:
"The WITH CHECK and WITH NOCHECK clauses cannot be used for PRIMARY KEY and UNIQUE constraints."

Author

Commented:
OK. Although I didn't get my problem resolved. I will close this question. Thanks to everyone for advice.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial