ConstraintException when querying SQlite database with C #

Question

I hope someone can help with my SQLite database problem.

I get a ConstraintException when querying my SQLite database using C #. The full error message is “ Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. ” I originally built this database using access that worked fine, but for various reasons I had to recreate it using SQLite.

To give a little background, this is a simple state planning program. Each Status has an associated Account and Schedule . I understand that Statuses and Schedule are 1: 1 and can be in the same table, but in order to continue developing the program, I divided them into two tables.

Below is an abridged version of my script table (this is enough to recreate the problem).

 PRAGMA foreign_keys = ON; CREATE TABLE Accounts (ID INTEGER PRIMARY KEY AUTOINCREMENT, Name char(100)); CREATE TABLE Statuses (ID INTEGER PRIMARY KEY AUTOINCREMENT, AccountId INTEGER REFERENCES Accounts(ID) ON DELETE CASCADE, Text char(140)); CREATE TABLE Schedule (ID INTEGER PRIMARY KEY REFERENCES Statuses(ID) ON DELETE CASCADE, StartDate char(255), Frequency INT); 

I had no problems until I created two Statues and linked them to the same Account .

 Accounts ID Name 1 Fred Blogs Statuses ID AccountId Text 1 1 "Some text" 2 1 "Some more text" Schedule ID StartDate Frequency 1 16/02/2011 1 2 16/02/2011 1 

The select statement Im, which throws an exception:

 SELECT Statuses.Id, Statuses.Text, Accounts.Id, Accounts.Name, Schedule.StartDate, Schedule.Frequency FROM [Statuses], [Accounts], [Schedule] WHERE Statuses.AccountId = Accounts.Id AND Statuses.Id = Schedule.Id 

If I run the same query but delete the ' Accounts.Id column, the query works fine.

See below the C # code I'm using, but I don't think this is a problem.

 public DataTable Query(string commandText) { SQLiteConnection sqliteCon = new SQLiteConnection(ConnectionString); SQLiteCommand sqliteCom = new SQLiteCommand(commandText, sqliteCon); DataTable sqliteResult = new DataTable("Query Result"); try { sqliteCon.Open(); sqliteResult.Load(sqliteCom.ExecuteReader()); } catch (Exception) { throw; } finally { sqliteCon.Close(); } return sqliteResult; } 

Any help would be appreciated. Thanks.

Answer 1

I found a way around this problem. If I select AccountId from the Schedule table and not the Accounts table, an exception will not be thrown. I don't seem to be able to run the SELECT statement containing two unique primary key columns.

So instead

 SELECT Statuses.Id, Statuses.Text, Accounts.Id, Accounts.Name, Schedule.StartDate, Schedule.Frequency FROM [Statuses], [Accounts], [Schedule] WHERE Statuses.AccountId = Accounts.Id AND Statuses.Id = Schedule.Id 

I ran

 SELECT Statuses.Id, Statuses.Text, Statuses.AccountId, Accounts.Name, Schedule.StartDate, Schedule.Frequency FROM [Statuses], [Accounts], [Schedule] WHERE Statuses.AccountId = Accounts.Id AND Statuses.Id = Schedule.Id 

Answer 2

The error occurs due to the identifier columns in the Status table and the Schedule table. If they are not important, remove the columns from the two tables.

Answer 3

I fixed the problem by first reading the schema, then clearing the data limits, and then reading the data again. eg:

 DataSet DS = new DataSet(); mytable = new DataTable(); DS.Tables.Add(mytable); DS.EnforceConstraints = false; SQLiteCommand command = DBconnection.CreateCommand(); command.CommandText = "select * from V_FullView"; SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly); mytable.Load(reader); mytable.Constraints.Clear(); reader = command.ExecuteReader(); mytable.Load(reader); reader.Close(); 

my V_FullView is a representation of four different tables. The constraints seem to be related to the first merged table (the name was unique on this, but replicated several times in the view)