INSERT INTO TABLE KEYED ONLY ON IDENTITY COLUMN RESULTS IN KEY VIOLATION (6.5) 
Author Message
 INSERT INTO TABLE KEYED ONLY ON IDENTITY COLUMN RESULTS IN KEY VIOLATION (6.5)

We have a couple of tables that are keyed on the first column which is an
IDENTITY column e.g. process ID. When we try and insert into the table.
There are no triggers involved or SELECT INTOs which are listed as known
problems in early 6.5 releases.

INSERT INTO (col2, col3, ....)
values (col2value, col3value, ....)

On more than one occasion,  a key violation has occurred (on more than one
of the tables).
Does anyone know how we can rectify this and its cause?

I seem to vaguely remember seeing a bug listed on this but now can't find it
on the Microsoft support site.

Regards

Matt P

Pracctice Ltd.



Sat, 20 Jul 2002 03:00:00 GMT
 INSERT INTO TABLE KEYED ONLY ON IDENTITY COLUMN RESULTS IN KEY VIOLATION (6.5)

HI

    SQL 6.5 has a problem where it generates duplicate values for the
Identity key...

    I have faced this before ... the latest service pack might have fixed
this.

    I would ofcourse suggest generating your own key use select(max) and
adding 1 to the returned value....

Laski...

/* --------------------------------------------------------------------*/

Quote:
> We have a couple of tables that are keyed on the first column which is an
> IDENTITY column e.g. process ID. When we try and insert into the table.
> There are no triggers involved or SELECT INTOs which are listed as known
> problems in early 6.5 releases.

> INSERT INTO (col2, col3, ....)
> values (col2value, col3value, ....)

> On more than one occasion,  a key violation has occurred (on more than one
> of the tables).
> Does anyone know how we can rectify this and its cause?

> I seem to vaguely remember seeing a bug listed on this but now can't find
it
> on the Microsoft support site.

> Regards

> Matt P

> Pracctice Ltd.



Sat, 20 Jul 2002 03:00:00 GMT
 INSERT INTO TABLE KEYED ONLY ON IDENTITY COLUMN RESULTS IN KEY VIOLATION (6.5)
DBCC CheckIdent

--
Thanks,
David Satz
Principal Software Engineer
Hyperion Solutions
(using VB6 SP3/MTS/SQL Server 6.5 SP5a)
(Please respond to the newsgroup.)


Quote:
> We have a couple of tables that are keyed on the first column which is an
> IDENTITY column e.g. process ID. When we try and insert into the table.
> There are no triggers involved or SELECT INTOs which are listed as known
> problems in early 6.5 releases.

> INSERT INTO (col2, col3, ....)
> values (col2value, col3value, ....)

> On more than one occasion,  a key violation has occurred (on more than one
> of the tables).
> Does anyone know how we can rectify this and its cause?

> I seem to vaguely remember seeing a bug listed on this but now can't find
it
> on the Microsoft support site.

> Regards

> Matt P

> Pracctice Ltd.



Sat, 20 Jul 2002 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. IDENTITY (MSSQL 6.5) - Primary Key violation

2. Primary Key Violations on IDENTITY column

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

4. On Insert with IDENTITY, I get Msg 2627 Violation of PRIMARY KEY constraint

5. violation of primary key on identity column

6. violation of primary key on identity column

7. Key Violation in Identity columns

8. Attempt to insert duplicate identity values in table (has identity and key fields)

9. DB Design: identity column as primary key and join key

10. DB Design: identity column as primary key and join key

11. Insert error with system generated key (identity column)


 
Powered by phpBB® Forum Software