System.Data.SQLite

Hi Guys,

This is my first time using SQLite and I've run into an issue.

I've got a SQLlite Database keeping a list of people and associated cost centres, it's pretty basic:

Two tables, both with an ID column which is unique, autoincrement and primary key. Some other various columns, name, costcentre, email etc. Most of which are set to not null. Nothing overly complex, it's very basic. People have a cost centre and I'd join the two tables on CostCentre.

I run this query:

 SELECT * FROM PEOPLE left JOIN (COSTCENTRES ON PEOPLE.COSTCENTRE = COSTCENTRES.COSTCENTRE) where wipenabled = 1

It runs fine in the designer in VS and in SQLiteAdministrator, but when I run it in code it fails:

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

 The code is:

using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + path))
            {
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                cmd.CommandText = sql;
                SQLiteDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
                dr.Close();

                conn.Close();

            }

Does anyone have any idea where I'm going wrong here? I've checked the 
DB and not ever turned constraints on, so I don't think it's that - The 
query seems okay as it runs in SQLiteAdmin. I'm inserting into a new DataTable Object, which as far as I know has no constraints.

any help appreciated! 

My guess woudl be something in your connection stirng is turning on foreign key enforcement, but the data in your database does not actually satisfy the foreign key constraints.  I'd guess from that query that you have a foreign key from people.costcentre to costcentres.costcentre?  If that is true, then try running this query in SQLiteAdministrator or some other tool:

If that returns any rows, then those rows violate the foreign key and need to be fixed.

It may be that nothing is enabling foreign key constraints in SQLite, but the data table is understanding the FKs in SQLite, trying to validate them, and failing.  The query above should still help finding the culprit rows.

Also googling that exception and error message returns a ton of useful suggestions *hint*hint*

I was running into the same issue, and couldn't figure out how to relax/turn off the table constraints. My way around it was to make sure the columns of my local table matched the columns being returned:

              Dim dt As DataTable = Nothing

             If dr.HasRows Then
                Dim drow As DataRow = Nothing
                Dim dcol As DataColumn = Nothing
                Do While dr.Read
                    If dt Is Nothing Then
                        dt = New DataTable(<Name of your table>)
                        For index As Integer = 0 To dr.FieldCount - 1
                            dcol = New DataColumn(dr.GetName(index), dr.GetFieldType(index))
                            dt.Columns.Add(dcol)
                        Next
                    End If
                    drow = dt.NewRow
                    For Each dcol In dt.Columns
                        drow(dcol.ColumnName) = dr.Item(dcol.ColumnName)
                    Next
                    dt.Rows.Add(drow)
                Loop
            End If