CODEWITHSUNDEEP
t-sqlprimary-key
Pimenta
Pimenta

Reputation: 1109

Issue with PK violation on insert

I have a scenario where almost all of the tables have issues with the PK value as follows. This results is a database error or the violation of the PK insert. When using the DBCC CheckIdent it displays an inconsistency between the next value and the current one. Can anyone have a reason for the mismatch happening on several tables?

Since this database is then replicate, I'm afraid this error will propagate across the environment.

I adapted this script to fix it, but really trying to figure out the root of the problem.

    /** Version 3.0 **/

    if object_id('tempdb..#temp') is not null
     drop table #temp

    ;
    with cte as (
        SELECT
            distinct
            A.TABLE_CATALOG AS CATALOG,
           A.TABLE_SCHEMA AS "SCHEMA",
           A.TABLE_NAME AS "TABLE",
           B.COLUMN_NAME AS "COLUMN",
           IDENT_SEED (A.TABLE_NAME) AS Seed,
           IDENT_INCR (A.TABLE_NAME) AS Increment,
           IDENT_CURRENT (A.TABLE_NAME) AS Curr_Value
           , DBPS.row_count AS NumberOfRows
        FROM INFORMATION_SCHEMA.TABLES A
        inner join INFORMATION_SCHEMA.COLUMNS B on b.TABLE_NAME = a.TABLE_NAME and b.TABLE_SCHEMA = a.TABLE_SCHEMA
        inner join sys.identity_columns IC on OBJECT_NAME (IC.object_id) = a.TABLE_NAME
        inner join sys.dm_db_partition_stats DBPS ON DBPS.object_id =IC.object_id 
        inner join sys.indexes as IDX ON DBPS.index_id =IDX.index_id 
        WHERE A.TABLE_CATALOG = B.TABLE_CATALOG AND 
              A.TABLE_SCHEMA = B.TABLE_SCHEMA AND 
             A.TABLE_NAME = B.TABLE_NAME AND 
             COLUMNPROPERTY (OBJECT_ID (B.TABLE_NAME), B.COLUMN_NAME, 'IsIdentity') = 1 AND 
             OBJECTPROPERTY (OBJECT_ID (A.TABLE_NAME), 'TableHasIdentity') = 1 AND 
             A.TABLE_TYPE = 'BASE TABLE'
    ) 
        select 'DBCC CHECKIDENT ('''+A.[SCHEMA]+'.'+a.[TABLE]+''', reseed)' command
            , ROW_NUMBER() OVER(ORDER BY a.[SCHEMA], a.[TABLE] asc) AS ID
            , A.Curr_Value
            , a.[TABLE]
        into #temp
        from cte A
        ORDER BY A.[SCHEMA], A.[TABLE]

    declare @i int = 1, @count int = (select max(ID) from #temp)
    declare @text varchar(max) = ''

    select @COUNT= count(1) FROM #temp

    WHILE @I <= @COUNT
    BEGIN 
        SET @text = (SELECT command from #temp where ID=@I)
        EXEC (@text + ';')
        print @text

        select Curr_Value OldValue, ident_current([TABLE]) FixValue, [TABLE] from #temp where ID=@I

        SET @I = @I + 1
        SET @text='';
    END
    go

Upvotes: 0

Views: 36

Answers (1)

Ra&#250;l D&#237;az
Ra&#250;l D&#237;az

Reputation: 36

maybe someone or something with enough permissions made a mistake by reseeding?

As simple as this:

create table testid (
    id int not null identity (1,1) primary key,
    data varchar (3)
)

insert into testid (data) values ('abc'),('cde')

DBCC CHECKIDENT ('testid', RESEED, 1)

insert into testid (data) values ('bad')

Upvotes: 1

Related Questions