Inserting into an Unique Index field

Question

Inserting into an Unique Index field

Jol

SSC-Addicted

Points: 429
More actions
September 28, 2005 at 3:20 pm

#111790

Hi,
I am trying to insert a row in a table that contains "acntid" as indexed field.The name of the index is "acntid_indx"..This index is set to be unique...
How do I insert a proper value in this field while inserting a row in this table?
Thanks in advance for the help
Jol

Kindest Regards,
Joel

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

you insert a value that does not already exist.

Can you please post an example of your table definition, and what your trying to accomplish, the way your question is worded is very confusing.

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 2

That logic is to be handled by the client application - the uniqueness constraint in the database prevents any data integrity issues and will return a 2601 error if a duplicate value is inserted.

If you are trying to write code to prevent entering dupes for that value, you can always look up values in that column, for example:

create table testcase (col1 varchar(10))

go

create unique index testcase_ind_1 on testcase (col1)

go

insert into testcase values ('abc');

insert into testcase values ('xyz');

go

select 1 from testcase where col1 = 'xyz'

If the above statement returns 1, then do not form the insert and notify the end user (if this is a UI screen) or the application (server side C++ code for example) that the value exists.

Or if you are getting bulk data from another source that needs to be inserted, write up SQL statement to ensure that only those records get inserted where the value does not exist in the testcase.col1 column and take corrective action for those which are dupes.

Jol SSC-Addicted Points: 429 More actions · Answer 3

The structure of my table is

Accounts

Name Address DOB Accountid

There is unique index Accountid_indx on the Accountid field and is auto incremented.

I am able to insert a row in this table From SQL SERVER by writing a SQL query...for eg

If the max vaule in the accountid column is 6 then my insert statement would be

Insert into accounts(...) values(...7)

So right now the rows of my table would look something like

... ... .... 7

... ... .... 6

... ... .... 5

... ... .... 4

Now when I try to insert a row from my application it gives me

"Cannot insert a duplicate value error"

So I think there is some counter in SQL Server that stores the auto incremented value of the index field...and it does not get updated when I manually insert a row from SQL.

So is there any way I can change the value of this counter..?

Thanks

Joel

Kindest Regards,

Joel

Ray M SSC-Insane Points: 21093 More actions · Answer 4

No, sql does not maintain a counter for a unique index field.

You should be able to change the acntID column to be an identity column.

But otherwise, there is no way to "Automatically" handle this in sql server.