DataTable.Load, One or more rows contain values violating non-null, unique, or foreign-key constraints

Question

I searched a lot, but could not find the solution.

I get the error:

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

I run DataTable.GetErrors() and see that some columns are set to Not NULL in SQL Compact Edition Database. And these columns are used in LEFT OUTER JOIN query so they are null when the query is run. (I can get the results when i run the query in Server Explorer in VS). The error occurs when trying to load the data in Datatable:

using (SqlCeCommand Cmd = new SqlCeCommand("Query HERE", "Connection HERE"))
                {
                    C.Open();
                    using (SqlCeDataReader Rdr = Cmd.ExecuteReader())
                    {
                        DataTable DT = new DataTable();
                        DT.Load(Rdr);
                        return DT;
                    }
                }

I tried so many solutions to overcome this however i was not able to solve it. I know about "EnforceConstraints" however as i do not use any dataset, i can not change that property.

I have seen this many times before typically with a DataSet populated by a query which returns two DataTables and the key is either NULL or does not join correctly between the two data tables. — Kane, Jan 06 '12 at 11:43
There is only one datatable as you see from the code. And as i said, i do not use any datasets. — tozlu, Jan 07 '12 at 13:26
See my new answer at older thread https://stackoverflow.com/questions/7807681/getting-a-constraints-exception-when-loading-a-datareader-in-a-datatable/44533184#44533184 for a simple workaround that applies in at least some cases. This question probably qualifies as a duplicate of that thread, although there is valuable information in both. — SQLServerSteve, Jun 13 '17 at 23:40
Possible duplicate of [Getting a constraints exception when loading a DataReader in a DataTable](https://stackoverflow.com/questions/7807681/getting-a-constraints-exception-when-loading-a-datareader-in-a-datatable) — SQLServerSteve, Jun 13 '17 at 23:43

score 10 · Accepted Answer · answered Nov 11 '12 at 22:31

I managed to solve this by getting the schema of the table, iterating through the rows of the schema table (which are columns of the actual table) and creating a column with same properties as the schema column (only difference is setting new column's AllowDBNull to true and Unique and AutoIncrement to false) and finally adding the new column to a fresh datatable which will later be filled with data of our actual table (by the help of a DataReader to get only data and not the schema).

Here is the code:

using (SqlCeConnection C = new SqlCeConnection(DBStr))
            using (Cmd)
            {   //using SqlCeCommand
                Cmd.Connection = C;
                C.Open();
                using (SqlCeDataReader Rdr = Cmd.ExecuteReader())
                {
                    //Create datatable to hold schema and data seperately
                    //Get schema of our actual table
                    DataTable DTSchema = Rdr.GetSchemaTable();
                    DataTable DT = new DataTable();
                    if (DTSchema != null)
                        if (DTSchema.Rows.Count > 0)
                            for (int i = 0; i < DTSchema.Rows.Count; i++)
                            {
                                //Create new column for each row in schema table
                                //Set properties that are causing errors and add it to our datatable
                                //Rows in schema table are filled with information of columns in our actual table
                                DataColumn Col = new DataColumn(DTSchema.Rows[i]["ColumnName"].ToString(), (Type)DTSchema.Rows[i]["DataType"]);
                                Col.AllowDBNull = true;
                                Col.Unique = false;
                                Col.AutoIncrement = false;
                                DT.Columns.Add(Col);
                            }

                    while (Rdr.Read())
                    {
                        //Read data and fill it to our datatable
                        DataRow Row = DT.NewRow();
                        for (int i = 0; i < DT.Columns.Count; i++)
                        {
                            Row[i] = Rdr[i];
                        }
                        DT.Rows.Add(Row);
                    }
                    //This is our datatable filled with data
                    return DT;
                }
            }

score 0 · Answer 2 · answered Jan 24 '22 at 10:54

0

In case it catches anyone else; in my SQLite database I had NULL entries stored as strings ("NULL"), simply fixing the data worked.

This is a SQLite specific issue as you can mix datatypes in columns!

answered Jan 24 '22 at 10:54

Ben Robinson

1,602
3
16
30

score 0 · Answer 3 · answered Jan 13 '23 at 12:15

This can also happen if you load data from a table where some strings are only different in upper and lower case. In my case it happened using SQLite (Microsoft.Data.SQLite). I could fix it by setting DataTable.CaseSensitive = true. Code example in C#:

protected virtual DataTable Get(string sqlQuery)
{
    DataTable dataTable = new DataTable() { CaseSensitive = true };
    DbProviderFactory factory = SqliteFactory.Instance;
    using (DbConnection connection = GetConnection(factory))
    {
        using (DbCommand command = factory.CreateCommand())
        {
            command.Connection = connection;
            command.CommandText = sqlQuery;

            connection.Open();
            dataTable.Load(command.ExecuteReader());
        }
    }

    return dataTable;
}

DataTable.Load, One or more rows contain values violating non-null, unique, or foreign-key constraints

3 Answers3

Linked