Indentity field causing primary key violation 
Author Message
 Indentity field causing primary key violation

I am using Microsoft access 97 as a user interface to work with an SQL
database. I am using ODBC to connect.

The problem is that the SQL server seems to forget what the last number was
in an Itdentity field.

Example :
    I have a table with an Identity field Call ID
    It is the Primary Key
    I added 35 consecutive records to the table

    {Some thing unkown happens}

    When I try to add the 36 record I get an error that tells me that the
new record can't be added due to a primary key violation.
    I deleted all the 35 records and then append the new record. The ID
field was 18.

    I have had this happen on 3 different Tables. The other two times I
deleted the table and started again.

Can anyone tell me what causes this?

Can anyone tell me how to get around this?

Is there a way of asigning what the next number will be?

Thanks for your Help

Ed



Wed, 28 Mar 2001 03:00:00 GMT
 Indentity field causing primary key violation

Quote:
> The problem is that the SQL server seems to forget what the last number
was
> in an Itdentity field.

> Example :
>     I have a table with an Identity field Call ID
>     It is the Primary Key
>     I added 35 consecutive records to the table

>     {Some thing unkown happens}

>     When I try to add the 36 record I get an error that tells me that the
> new record can't be added due to a primary key violation.
>     I deleted all the 35 records and then append the new record. The ID
> field was 18.

>     I have had this happen on 3 different Tables. The other two times I
> deleted the table and started again.

> Can anyone tell me what causes this?

It just happens - MS won't guarantee it will always be correct

Quote:

> Can anyone tell me how to get around this?

Run dbcc checkident against the offending table

Quote:
> Is there a way of asigning what the next number will be?

You can explicitly set values in an identity column by preceding the insert
with
set identity insert on
insert your row
then set identity insert off
..but if you're going to provide a value every time then
you don't need the identity column...

Mike



Thu, 29 Mar 2001 03:00:00 GMT
 Indentity field causing primary key violation
Ed,

The reason why the identity field usually gets out of sync is due to an
unclean shutdown.  The value of the field is stored in memory due to
performance reasons and as the machine is shutdown uncleanly it does not get
written away.  The way around this is to run dbcc checkident() against the
tables that have an identity field.

I run this as a startup procedure and when I reboot my machines each night
the consistency is checked but it could be run periodicaly if you wish.
Here is the script.  All output will be placed in the errorlog

use master
go
CREATE PROCEDURE sp_checkident_all_tables
AS


set nocount on
DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects
WHERE type = 'U' and id in (select distinct id from syscolumns where status
& 128 = 128)

OPEN tnames_cursor


BEGIN
/*

  -1, or 0, all three cases must be tested. In this case, if
  a table has been dropped since the time this stored
  procedure was executed, it will be skipped. A
  successful fetch (0) will cause the DBCC within the
  BEGIN..END loop to execute.
*/

BEGIN
  PRINT " "

"  *************"

  PRINT " "

END

END
PRINT " "
PRINT " "

*************"

PRINT " "
PRINT "DBCC has been run against all user-defined tables."
DEALLOCATE tnames_cursor

GO

create procedure Check_All_Databases as


set nocount on
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM sysdatabases
WHERE name not in ("distribution","master","model","msdb","pubs","tempdb")
OPEN dbnames_cursor


BEGIN

BEGIN
  PRINT " "

*************"

  PRINT " "

END

END
PRINT " "
PRINT " "


PRINT " "
PRINT "DBCC has completed for all database."
DEALLOCATE dbnames_cursor
GO

exec sp_makestartup Check_All_Databases
go

Please ensure that you test this code in your own environment.

Steve Robinson
SQLServer MVP


Quote:

>I am using Microsoft access 97 as a user interface to work with an SQL
>database. I am using ODBC to connect.

>The problem is that the SQL server seems to forget what the last number was
>in an Itdentity field.

>Example :
>    I have a table with an Identity field Call ID
>    It is the Primary Key
>    I added 35 consecutive records to the table

>    {Some thing unkown happens}

>    When I try to add the 36 record I get an error that tells me that the
>new record can't be added due to a primary key violation.
>    I deleted all the 35 records and then append the new record. The ID
>field was 18.

>    I have had this happen on 3 different Tables. The other two times I
>deleted the table and started again.

>Can anyone tell me what causes this?

>Can anyone tell me how to get around this?

>Is there a way of asigning what the next number will be?

>Thanks for your Help

>Ed



Sat, 31 Mar 2001 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Paradox Autoincrement Fields Cause Key Violation -- Help!

2. Q: Primary key constraint violation with datetime field

3. Joined Primary Key in Foxpro (Make 1 Primary Key from 2 fields)

4. DataEnvironment causes error on Primary key field at rebind

5. Primary key field causing problems

6. Joined Primary Key in Foxpro (Make 1 Primary Key from 2 fields)

7. Violation of PRIMARY KEY constraint Cannot insert duplicate key in object

8. Primary Key to Indentity Column

9. Paradox Key Violation - changing a keyed field

10. Reordering of key fields leads to key violations (Pdox 5.0)


 
Powered by phpBB® Forum Software