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