SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Identity column value should not be increased if there's pbm in insertion of record.


Identity column value should not be increased if there's pbm in insertion of record.

Author
Message
Joy Smith San
Joy Smith San
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6350 Visits: 3200
Dear All,

I have a table which has got an IDENTITY COLUMN. There is another column in the same table which is primary key.
When user inserts a new record, I don't check for the uniqness of the value inserted for the primary key field.
Instead I capture the error code when SQL server throws primary key constraint error (if a duplicate entry is made.).
It works fine...The problem is that, When a duplicate entry is made, the record is not entered...
but IT INCREASES THE IDENTITY COLUMN by 1. I don't want this. Am using the IDENTITY column value for display also.
So I want it in sequece. Is there any solution to this problemm.?
(ie, Identity column value should not be increased if there is any pbm in insertion of record.)

Thanks in advance,
Santhu.
ChiragNS
ChiragNS
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6655 Visits: 1865
you will have to validate your data being inserted before inserting.

"Keep Trying"
Joy Smith San
Joy Smith San
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6350 Visits: 3200
Thank you.....

Infact I wanted to avoid that..At present I do that valdation only when error comes...So there's no way to achieve this..rite.?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223400 Visits: 46297
nairsanthu1977 (11/4/2008)
Thank you.....
So there's no way to achieve this..rite.?


Not unless you want to do your own form of auto-increment column.
Why are the values of the identity column important?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Joy Smith San
Joy Smith San
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6350 Visits: 3200
We use Identity column as 'CODE' s...(cost code, branch code, etc.)
I was against it, but manager insisted to use identity column for this.
Thats y IDENTITY column has become the key value in the database.
(By the way, The primary key I mentioned is not CODE but we made Description as Primary key....)
Michael Earl-395764
Michael Earl-395764
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13165 Visits: 23078
Unfortunately, that is really a misuse of an identity column. The are designed to be used as meaningless primary keys so having gaps is unimportant. There is no real way around this. It is by design.

If you really need to ensure they are in order and have no gaps, you have to serialize inserts into the table. To do this, the most common practice is to keep another table with a single record that has the current identity value. When inserting a new record, lock the table that has the current identity value and use this value for your insert. Only unlock the current identity value table after you commit your record. This will ensure you cannot have a second insert before you commit.

I would not advocate this. The best solution is to ensure your identity values are meaningless and that gaps do not matter.
steveb.
steveb.
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10540 Visits: 7195
If you already have a primary key then what is the need for the Identity column?

if is for display purposes only , you could use ROW_NUMBER() and generate a sequence number when you run a query
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71388 Visits: 40930
can you change the way the data gets inserted? if an application is trying to do an INSERT, can you make it call a stored proc instead?
what happens if the value already exists? what do you do then?
do you just want to return the Identity that matches the PK you tried to insert?

in a procedure you could try something like
If Exists(select PK from YourTable where PK=@PK)
--Return the Indentity
ELSE
--INSERT the new PK

to avoid the Identity() being triggered on attempted insert.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search