C# Sqlite throwing ConstraintException with DataTable

Question

I'm using C#/Mono to access a Sqlite db.

The following query works fine in any database app, but DataTable keeps complaining when I try to load the results.

Here's a snippet

    cmd.CommandText = @"SELECT 
                          sectors.name AS sector_name,
                          domains.name AS domain_name
                        FROM
                          sectors_domains
                          INNER JOIN domains ON (sectors_domains.domain = domains.id)
                          INNER JOIN sectors ON (sectors_domains.sector = sectors.id)";

    using (var rdr = cmd.ExecuteReader())
    {
        using (DataTable dt = new DataTable())
        {
            dt.load(rdr)
        }
    }

This throws an error:

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

The fields being selected (ie, both .name fields in the respective tables) are constrained as Unique and Non-null.

Are you absolutely sure they're unique? What happens if you make it a `SELECT DISTINCT` and try it? — CJBS, Feb 27 '14 at 07:38
Have a look at this: http://stackoverflow.com/a/7029713/3063884 If that doesn't work, consider sharing your data. — CJBS, Feb 27 '14 at 07:53
Thanks for that. Apparently, even though the *rows* are unique, there are columns that are repeated (as you'd expect in a many-many result). Is there a way around this? — cyrus, Feb 27 '14 at 08:08
Perhaps there's a `UniqueConstraint` on the specific data table row. Have a look at: http://msdn.microsoft.com/en-us/library/st1t2c35%28v=vs.110%29.aspx It seems like it's inferring this from the data returned from the query. Or disable them, as suggested by @Uwe below. — CJBS, Feb 27 '14 at 08:14
Have a look at an answer I posted. I can't batch-paste code in a comment. It's just a different approach to load the DataTable. — CJBS, Feb 27 '14 at 08:37

score 3 · Answer 1 · answered Feb 27 '14 at 08:06

If you don't need the constraints (or for debugging to find the problem) you could try to turn them off when loading:

using (var rdr = cmd.ExecuteReader())
{
    using (DataSet ds = new DataSet())
    {
        using (DataTable dt = new DataTable())
        {
            ds.Tables.Add(dt);
            ds.EnforceConstraints = false;
            dt.Load(rdr);
            rdr.Close();
        }
     }
}

Beware of typos :-). This was freehandcoded.

Thanks! I tried this, but I'm still getting the same error. – cyrus Feb 27 '14 at 08:13 — cyrus, Feb 27 '14 at 08:13

score 3 · Accepted Answer · answered Feb 27 '14 at 08:36

Try a different approach to loading the data. (Disclaimer: I haven't tried this)

DataTable dataTable;
cmd.CommandText = @"SELECT 
    sectors.name AS sector_name,
    domains.name AS domain_name
    FROM sectors_domains
    INNER JOIN domains ON (sectors_domains.domain = domains.id)
    INNER JOIN sectors ON (sectors_domains.sector = sectors.id)";


// Create data adapter
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);

// Populate dataTable based on DB query
da.Fill(dataTable);
da.Dispose();

... just ... thanks! A five year post prevented me from making an insurance claim on my monitor. — IdusOrtus, May 21 '19 at 21:48

C# Sqlite throwing ConstraintException with DataTable

2 Answers2

Linked