Table insert fails. HELP ! (please) 1

[bakerm]

The following error occurs when I try to insert a record into this db. The record with duplicate info was in the table previously, I have made sure that the duplicate record has been removed.

The index for this table is set using the following:

CREATE UNIQUE INDEX [I_SPTBLOP_OPTION_CODE] ON [dbo].[SPTBLOP]([OPTION_CODE]) ON [PRIMARY]

When I run the insert statement via a ADO execute command in VB I get the following error:

Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'SPTBLOP' with unique index 'I_SPTBLOP_OPTION_CODE'.
The statement has been terminated.

I am not very well versed in SQL so any help would be greatly appreciated.

Thanks, Mark

 

[mooki]

I didn't quite understand whether you had records before you run the insert statement or not !
any how i will explain the concept of unique index (if it is already understood, please forgive me...)

You have created a unique index on table SPTBLOP, and the column for that index is OPTION_CODE.

It means that :
1) you have an index of one column called (OPTION_CODE)
2) Sql-server creates a constraint so there will be only ONE RECORD in that table with the same OPTION_CODE !!!

what to do ?
1) read some on indexes concepts ! very important issue in Databases !!!
2) if you do need Only that column as uniqe, make sure that you don't insert records that already exists !
2 ways doing that :
a) create a Joined select or sub-select for Existence.
b) In the Index properties you can choose : Ignore Duplicate.

that is all, hope i helped a bit.

 

[bakerm]

Thanks for your help mookie.
Can you recommend any good reading on SQL "Beginner" level?