asked
Create Index
Hi, Im trying to create an index using IGNORE_DUP_KEY = ON in sql server 2000, when executing the query it showing syntax error. help me to correct the error.. query create unique index ind_dup_1 on dup_index(firstname) with (IGNORE_DUP_KEY = ON)
If you already have data in the table, with duplicate values, then the unique index cannot be created. To add this index to a table where duplicate values already exists, you will have to de-dupe the data first. The `IGNORE_DUP_KEY` option only controls the response when you subsequently try to insert duplicate values into the table. - ON : means that a warning message will be given and only the rows violating the unique constraint will fail to b inserted - OFF (the default) : means that the whole insert statement will fail and will be rolled back ---- Edit : follow the comments on this answer and we eventually get to the answer: create unique index ind_dup_1 on dup_index(firstname) with IGNORE_DUP_KEY
haven't got a 2000 instance available to me to test but can you try `create unique index ind_dup_1 on dup_index(firstname) with IGNORE_DUP_KEY`
I have created new table and tried to create index for that table..there is no values..
Haven't got a 2000 instance to test on, but try removing the brackets `create unique index ind_dup_1 on dup_index(firstname) with IGNORE_DUP_KEY = ON`
i would have tried removing brackets but it showing the same error.
can you post the table creation script too - I've just mocked this up on my machine and it works ok.
create table dup_index(id int,firstname varchar(10) ,lastname varchar(10), city varchar(10)) create unique index ind_dup_1 on dup_index(firstname) with IGNORE_DUP_KEY = ON
Forget my comment about removing the brackets - you need those. However `create table dup_index(id int,firstname varchar(10) ,lastname varchar(10), city varchar(10))` `create unique index ind_dup_1 on dup_index(firstname) with (IGNORE_DUP_KEY = ON)` works fine for me on a 2008 instance running under 2000 compatibility.
I think that's what I was trying to get to when I suggested removing the brackets
On SQL 2000 it should be without the `=` create unique index ind_dup_1 on dup_index(firstname) with IGNORE_DUP_KEY ON Created a test table on SQL 2008, and generated script including the index with the script compatibility set to SQL 2000..
The 2000 syntax is different - it is create unique index ind_dup_1 on dup_index(firstname) with IGNORE_DUP_KEY No brackets, no =, no ON
