Bug #36364 Constraint error when doing Fill schema before Fill Adapter
Submitted: 27 Apr 2008 8:51 Modified: 29 Apr 2008 14:43
Reporter: Anson Chapman Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:5.2 Connector/Net OS:Windows (XP - SP2)
Assigned to: CPU Architecture:Any
Tags: dataadapter, dataset, DataTable

[27 Apr 2008 8:51] Anson Chapman
Description:
When doing the Fill method of the DataAdapter with the FillSchema statement I get the following error:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints

This is returned when the "MyDGdaAdapter.Fill(MyDGDataset)" is executed.
Remove the "MyDGdaAdapter.FillSchema(MyDGDataset, SchemaType.Mapped)" instruction and all is okey.

Note: This only occurs if the select SQL statement contains a non-unique index.

I need the mapped schema for size of string fields

The code is this

Public Function GetDGData(ByVal sDB As String, ByVal selectCommand As String) As DataTable

        Try
          Call ClearErrors("Get DG Data")
          ' Create a connection string
          If sDB = "GENSYS" Then
            Call MyConnectString("GENSYS", True)
          Else
            Call MyConnectString(gGeneral.DataDB, True)
          End If
          ' Create a new data adapter based on the specified query.
          MyDGdaAdapter = New MySqlDataAdapter(selectCommand, MyDGConnString)
          ' Create a command builder to generate SQL update, insert, and
          ' delete commands based on selectCommand. These are used to
          ' update the database.
          MyDGCmdBuilder = New MySqlCommandBuilder(MyDGdaAdapter)
          ' Populate a new data table and bind it to the BindingSource.
          MyDGdtDataTable = New DataTable()
          MyDGDataset = New DataSet()
          MyDGdaAdapter.FillSchema(MyDGDataset, SchemaType.Mapped)
          MyDGdaAdapter.Fill(MyDGDataset)
          Return (MyDGDataset.Tables(0))
        Catch eX As MySqlException
          Call SetMySQLErrors(eX, "Get Data - MySql-DataGridView")
        End Try
        Return (Nothing)
  End Function

How to repeat:
Create a data table with more than one index field.

Individually the fields are non-unique (i.e. contain duplicate entries)
Together the indexs form a unique composit primary key

ex. Field_A - Non unique index
    Field_B - Non unique index
    Field_C - Non unique index
    Field_D - Non unique index
    Field_E  

A,B,C,D - Primary Key

Now select one of the non-unique index's 
Select Field_A From DataTable and use it in the data adapter
 
Works fine without the FillSchema instruction get error otherwise.

If ALL of the indexs are selected NO error with or without the FillSchema instruction.

NOTE: the error is NOT a MySQL exception - It should be

NOTE: Select * from data table works fine
Now select one of the non-unique index fields 
Select Field_A From DataTable and use it in the data adapter

Suggested fix:
Constraint error should NOT occur with SELECT statements only INSERT or UPDATE.
[29 Apr 2008 14:43] Reggie Burnett
This can be handled but it is a ton of work.  Essentially the server tells me if a field belongs in a key and doesn't change that info just because you didn't include all columns of the key in the query.  So, the only way to fix it is to override FillSchema and, for every table in the query, query the indexes from the server and, if all the columns are not present, not mark the column as unique.

I'm marking this as won't fix because this bug, as it exists now, won't be fixed.  The above solution is a ton of work so I have created a worklog and will target this fix for the 6.0 release.  In the meantime I would suggest either not calling FillSchema or disabling constraint checking on the datatable first.