Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Identity column value should not be increased if there's pbm in insertion of record. Expand / Collapse
Author
Message
Posted Monday, November 3, 2008 11:53 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:46 AM
Points: 1,593, Visits: 2,661
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.
Post #596334
Posted Tuesday, November 4, 2008 12:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
you will have to validate your data being inserted before inserting.

"Keep Trying"
Post #596353
Posted Tuesday, November 4, 2008 12:53 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:46 AM
Points: 1,593, Visits: 2,661
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.?


Post #596362
Posted Tuesday, November 4, 2008 1:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:57 AM
Points: 40,618, Visits: 37,085
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 2008, MVP
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

Post #596369
Posted Tuesday, November 4, 2008 2:18 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:46 AM
Points: 1,593, Visits: 2,661

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....)








Post #596386
Posted Tuesday, November 4, 2008 5:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
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.
Post #596478
Posted Tuesday, November 4, 2008 5:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
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
Post #596484
Posted Tuesday, November 4, 2008 6:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:31 AM
Points: 12,962, Visits: 32,501
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #596515
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse