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

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

  • you will have to validate your data being inserted before inserting.

    "Keep Trying"

  • 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.?

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

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

  • 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

  • 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!

Viewing 8 posts - 1 through 7 (of 7 total)

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