An adventure in my VB6 (using OO4O) upgrading to share as more of a gripe than anything. Spent many hours late last night wrestling with this infamous error that doesn't seem to have any hard and fast causes that jump out at you:
"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."
Except none of the columns I queried had nulls and all constraints were met. BTW, I'm using v. 12.1 of the Oracle managed provider targeting 4.5 with a Reader loading a DataTable like so...
Using cmd As New OracleCommand(sql, conn)
Dim P1 As OracleParameter = New OracleParameter()
P1.ParameterName = "P1"
P1.OracleDbTypeEx = OracleDbType.Varchar2
P1.Direction = ParameterDirection.Input
P1.Value = pumpType
cmd.Parameters.Add(P1)
Using dr As OracleDataReader = cmd.ExecuteReader()
ResultSet = New DataTable
ResultSet.Load(dr)
End Using
End Using
Seemed like a lot of people had issues with the Entity Framework, tableAdapters and such but that wasn't my case.
Here is the original query as it had worked fine in VB6 for many years:
sql = ""
sql = sql & " SELECT " & vbCrLf
sql = sql & " HOUSING_COVER_NUM," & vbCrLf
sql = sql & " Description," & vbCrLf
sql = sql & " RING_NUMBER," & vbCrLf
sql = sql & " FITTING_CODE" & vbCrLf
sql = sql & " FROM PUMP_HOUSING_COVER HC, " & vbCrLf
sql = sql & " PUMP_PRODUCTLINE PL " & vbCrLf
sql = sql & " WHERE HC.PRODUCTLINEID = PL.PRODUCTID AND " & vbCrLf
sql = sql & " PL.productLine = :P1 " & vbCrLf
sql = sql & " ORDER BY HC.HOUSING_COVER_NUM "
Quite simple as you can see and although I had gotten a few others to work fine already I figured my noobness was not doing something simple that this particular one required for some reason. I mean how can I suddenly be getting constraint errors on a query that has been working for years and still is today? These are also static tables for the most part. One only has 2 records and the other about 40. None of the Googling offered much help except in the obvious cases where there really is an issue with one of the noted constraints. Some even claimed they got this error because of parameter size issues. Anyway, I tried taking columns out, modifying the join clause in all manners, switching the order of the 2 tables, but nothing that would at least run gave proper results.
Anyway, in the end I found someone that had mentioned an issue with a unique key of multiple columns, which indeed one of the tables had though I was not pulling in both of those UK columns so I added it to the Select, but still no joy. Then I got to thinking that it had to be somehow related to that column, which was also the foreign key dependent on the other table. So that led me to totally rewrite the query as a nested select (I think that's the name) and I could finally go to bed at 5 am.
sql = ""
sql = sql & " SELECT " & vbCrLf
sql = sql & " HOUSING_COVER_NUM," & vbCrLf
sql = sql & " Description," & vbCrLf
sql = sql & " RING_NUMBER," & vbCrLf
sql = sql & " FITTING_CODE," & vbCrLf
sql = sql & " PRODUCTLINEID" & vbCrLf '<<< - added this column which is part of UK with Housing_Cover_Num
sql = sql & " FROM PUMP_HOUSING_COVER " & vbCrLf
sql = sql & " WHERE PRODUCTLINEID = " & vbCrLf
sql = sql & " (SELECT PRODUCTID " & vbCrLf '<<< - changed joined to nested select which is still a join of sorts
sql = sql & " FROM PUMP_PRODUCTLINE " & vbCrLf
sql = sql & " WHERE productLine = :P1 )" & vbCrLf
sql = sql & " ORDER BY HOUSING_COVER_NUM "
Without BOTH of those changes, such a simple query wouldn't work.