CODEWITHSUNDEEP
t-sqlsybase
czchlong
czchlong

Reputation: 2594

Sybase complains of duplicate insertion where none exists

I have moved some records from my SOURCE table in DB_1 into an ARCHIVE table in another DB_2 (ie. INSERTED the records from SOURCE into ARCHIVE and then DELETED the records from SOURCE.)

My SOURCE table has the following index created as SOURCE_1:

CREATE UNIQUE NONCLUSTERED INDEX SOURCE_1
    ON dbo.SOURCE(TRADE_SET_ID, ORDER_ID)

The problem is - when I try to insert the rows back into SOURCE from ARCHIVE, Sybase throws the following error:

Attempt to insert duplicate key row in object 'SOURCE' with unique index 'SOURCE_1'

And, of course, subsequently fails the insertions.

I confirmed that my SOURCE table does not have these duplicates because the following query returned empty:

select * from DB_1.dbo.SOURCE
join DB_2.dbo.ARCHIVE
on DB_1.dbo.SOURCE.TRADE_SET_ID = DB_2.dbo.ARCHIVE.TRADE_SET_ID
AND DB_1.dbo.SOURCE.ORDER_ID = DB_2.dbo.ARCHIVE.ORDER_ID

If the above query returned nothing, then that means I haven not violated my unique index constraint on the 2 columns, however Sybase complains that I have.

Does anyone have any ideas on why this is happening?

Upvotes: 1

Views: 4777

Answers (1)

Tom A
Tom A

Reputation: 1682

If Sybase is anything like SQL Server in this regard (Which I'm more familiar with), I would suspect that the index is blocking the insert. Try disabling the index (along with any other indexes or autoincrement columns) on your archive version before copying over to it, then re-enabling. Its probable that Sybase would try to automatically create IDs for the insertions, which would interfere with the existing records.

Upvotes: 1

Related Questions