asked on

Create Unique Constraint without existing data validation

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,

RiteshShah

CREATE UNIQUE NONCLUSTERED INDEX [IX_DupIndex_C1C2]
ON [t]
(
[c1] ASC,
[c2] ASC
) WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY]
GO

Ora_Techie

ASKER

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.

SOLUTION

SinghAmandeep

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RiteshShah

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

Ora_Techie

ASKER

@Ritesh,

Please re-read my question if you don't mind. I don't want to do that.

RiteshShah

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

Ora_Techie

ASKER

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,

Ora_Techie

ASKER

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

ASKER CERTIFIED SOLUTION

RiteshShah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ora_Techie

ASKER

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