Friday 18 May 2012

IDENTITY Columns Violation


We all know about the identity columns of SQL Server and how important it is. This article is related to it but I am representing this article to solving a common proble.
First take a quick look about the identity columns and how it works. Then we are going to discuss about the problem and how to solve it programmatically.
Definition of Identity Columns
An IDENTITY column contains a value for each row, generated automatically by Adaptive Server that uniquely identifies the row within the table.
Each table can have only one IDENTITY column. You can define an IDENTITY column when you create a table with a create table or select into statement, or add it later with an alter table statement. IDENTITY columns cannot be updated and do not allow nulls.
You define an IDENTITY column by specifying the keyword identity, instead of null or not null, in the create table statement. IDENTITY columns must have a datatype of numeric and scale of 0. Define the IDENTITY column with any desired precision, from 1 to 38 digits, in a new table:
CREATE TABLE  table_name
             (column_name  INT NOT NULL IDENTITY(1,1))

The Problem comes for Identity
One of my friends told me that, he has a problem for IDENTITY columns. He told me that when he make the INSERT entry into table objects, he never check the uniqueness of the records for that the primary key violation error occurs. He just shows the error.
The problem is the table objects contain identity columns. The value of the identity columns increases each time, even the PK violation exists.
The problem is mentioned by T-SQL statements
Step-1 [ Create the Base Table with IDENTITY columns ]
-- Table defination
DROP TABLE tbl_Example
CREATE TABLE tbl_Example
    (
       ROLL     INT          NOT NULL PRIMARY KEY,
       SNAME    VARCHAR(50)  NULL,
       SCLASS   INT          NULL,
       ROWNUM   INT          NOT NULL IDENTITY(1,1)
    ) 
Step-2 [ Normal Insertions of Values ]
INSERT INTO tbl_Example
            (ROLL,          
                   SNAME,   
                   SCLASS)
VALUES (1, 'JOYDEEP', 1)                     

INSERT INTO tbl_Example
            (ROLL,          
             SNAME,   
             SCLASS)
VALUES (2, 'SUKAMAL', 1)
Step-3 [ See the Output ]
SELECT * FROM tbl_Example

-- Output
ROLL  SNAME       SCLASS      ROWNUM
----  -----       ------      ------
1     JOYDEEP     1           1
2     SUKAMAL     1           2

Step-4 [ Make an Invalid entry that generate an error ]
-- Violation of Primary Key
INSERT INTO tbl_Example
            (ROLL,          
             SNAME,   
             SCLASS)
VALUES (2, 'RAJESH', 1)

-- Output
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__tbl_Exam__44C28DB623BDA346'.
Cannot insert duplicate key in object 'dbo.tbl_Example'.
The statement has been terminated.

Step-5 [ Now Make the Correction entry after correction of data ]
-- Correction of Entry
INSERT INTO tbl_Example
            (ROLL,          
             SNAME,   
             SCLASS)
VALUES (3, 'RAJESH', 1)

Step-6 [ Look the INDENTITY columns has Increased it gives 4 instead of 3 ]

SELECT * FROM tbl_Example

--Output
ROLL        SNAME       SCLASS      ROWNUM
----        -----       ------      ------
1           JOYDEEP     1           1
2           SUKAMAL     1           2
3           RAJESH      1           4

Note that: It is the Normal behavior of the IDENTITY columns. It maintains the uniqueness. Here we are going to break it depends on our needs and it is not good for development. However I am just demonstrating it that we can do it if needed.
Here I am going to make a stored procedure to demonstrate it.
CREATE PROCEDURE my_proc
    (
       @param_roll   INT,
       @param_name   VARCHAR(50),
       @param_calss  INT
    )
AS
   DECLARE @v_NOREC INT
   BEGIN
          BEGIN TRY
            BEGIN TRANSACTION
                  INSERT INTO tbl_Example
                             (ROLL,    
                                            SNAME,   
                                            SCLASS)
                          VALUES (@param_roll, 
                                  @param_name,
                                  @param_calss)           
            COMMIT TRANSACTION
          END TRY
          BEGIN CATCH
            ROLLBACK TRANSACTION
            PRINT 'ERROR EXISTS'
            SELECT @v_NOREC=COUNT(*) FROM tbl_Example
            DBCC CHECKIDENT (tbl_Example, reseed, @v_NOREC)
          END CATCH
   END   
Now we execute it and see the result step by step.
   -- Execution-1
   EXECUTE my_proc
           @param_roll   = 1,
           @param_name   = 'JOYDEEP',
           @param_calss  = 1
          
   SELECT * FROM tbl_Example
 
   ROLL     SNAME       SCLASS      ROWNUM
   1        JOYDEEP     1           1
   -- Execution-2
   EXECUTE my_proc
           @param_roll   = 2,
           @param_name   = 'SUKAMAL',
           @param_calss  = 1
          
  

   SELECT * FROM tbl_Example
   ROLL     SNAME       SCLASS      ROWNUM
   1        JOYDEEP     1           1
   2        SUKAMAL     1           2
   -- Execution-3 [ Primary Key Error ]
   EXECUTE my_proc
           @param_roll   = 2,
           @param_name   = 'SANGRAM',
           @param_calss  = 1

   0 row(s) affected)
    ERROR EXISTS
    Checking identity information: current identity value '3', current column value '2'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
          
   SELECT * FROM tbl_Example
   ROLL     SNAME       SCLASS      ROWNUM
   1        JOYDEEP     1           1
   2        SUKAMAL     1           2
      -- Execution-4       
   EXECUTE my_proc
           @param_roll   = 3,
           @param_name   = 'SANGRAM',
           @param_calss  = 1   
          
   SELECT * FROM tbl_Example
   ROLL     SNAME       SCLASS      ROWNUM
   1        JOYDEEP     1           1
   2        SUKAMAL     1           2
   3        SANGRAM     1           3  
    
Hope you like it.

Posted by: MR. JOYDEEP DAS









6 comments:

  1. Good Work Joydeep.Nice solution.

    ReplyDelete
  2. Good One.

    There is any option to find out the identity value storage.

    ReplyDelete
    Replies
    1. Thanks "Sukaml" to provide your valuable time on it.
      I think you want to mean where it stores...i think. Belive me i personally don't know about it. If you know the answer please inform me as my reader will also helpful by it.

      I think you are happy to know about, i am trying to share my knowledge of BI with my readers. As you asking me from very long time. I need you and my readers support to provide BI related tutorial.
      http://sqlknowledgebank.blogspot.in/2012/05/data-warehousing.html

      Thanking you.

      Delete
  3. Replies
    1. Thanks "Sridhar Nalluri" to provide your valuable time on my article. I am requesting you to join my site on my web.

      Delete