Violation of UNIQUE KEY constraint

[HestonJames]

Hello Guys,

I'm recieving the error "Violation of UNIQUE KEY constraint 'UK_OUI' Cannot insert duplicate key" when running this query and I cant quite gather why, from my understanding, the query should only insert records from ##MessageStaging which dont already exist in the OUI table.

INSERT 
  INTO OUI
     ( OUI 
     , Manufacturer )
SELECT LEFT(##MessageStaging.DeviceAddress, 8)
     , 'Unknown OUI, ' + CONVERT(VARCHAR(25), GETDATE(), 121)
  FROM ##MessageStaging
LEFT OUTER
  JOIN OUI
    ON OUI.OUI = LEFT(##MessageStaging.DeviceAddress, 8) 
 WHERE OUI.OUI IS NULL

Any ideas?

Many thanks,

Heston

 

[RiverGuy]

What column(s) is/are the unique constraints on?

 

[SQLSister]

In addition to the possibility that there are more unique constraints than just the id column (as River guy suggessted), you might consider if there are duplicates within the temp table itself. Try a distinct in the select and see if it returns fewer records.

I have a couple of other concerns about your code. First, do you really need a global temp table? Those are in my experience very dangerous to use as other people can access them at the same time that you are using them.

Second why not actually just put the left 8 characters into the temp table rather than needing to use a function to compare to the table you want. That should help performance.

"NOTHING is more important in a database than integrity." ESquared

 

[HestonJames]

Hello to both of you, thanks for taking the time to reply.

From what I can see the unique key constrain appears to be on the OUI_ID column and the OUI column.

SQLSister, I forgot to mention, thats not actualy a global temp table, its just a standard single # temp table, however, the script is executed using Adobe ColdFusion which uses # signs to define its variables, so I escape it with a double ## but its actualy only processed and sent to SQL Server as a single #.

I have only take the LEFT(, 8) in this query, I use the remaining charecters from the string a subsequent queries in the transaction, thats why I load the full address into the temp table.

I do have multiple/duplicate entries in the staging table, is this likely to cause an issue? Idealy it would run through and not be a problem.

Thanks again,

Heston

 

[SQLSister]

I do have multiple/duplicate entries in the staging table, is this likely to cause an issue?

Yep that would be your problem. Did you try it with the distinct?

"NOTHING is more important in a database than integrity." ESquared

 

[MDXer]

Is this running as a stored procedure or as inline code from Cold Fusion? If it is inline you are setting yourself up for possible trouble.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[RyanEK]

Hi,

Try instead the following condition to avoid the constraint error...

INSERT  
  INTO OUI ( OUI , Manufacturer )
SELECT 
  LEFT(##MessageStaging.DeviceAddress, 8) , 'Unknown OUI, ' + CONVERT(VARCHAR(25), GETDATE(), 121) FROM ##MessageStaging t1
and not exists (select oui from OUI t2 where  t1.oui = t2.oui)

Ryan

 

[HestonJames]

Hello All,

Thanks for the suggestions, I really appreciate it. For now I've implemented and given some short tests to SQLSister's solution of using DISTINCT in my select statement, which appears to work quite nicely.

I need to build myself a more extensive test data set but for the moment I'm quite happy that has solved my problem.

MDXer, at the moment this is running as dynamic code from ColdFusion, I will look to porting it accross to a stored proceedure when I get chance and I'm convinced the entire process is working correctly. Just out of interest, why do you forsee it being a problem running from ColdFusion?

Ryan, I'm sure your solution would work all the same, I just opted for SQLSisters solution as that required less work to my code.

Cheers,

Heston