StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POConstraintException when querying SQlite database with C#
text
Body
copied!<p>I’m hoping somebody will be able to help with my SQLite database problem. </p> <p>I’m receiving a <code>ConstraintException</code> when querying my SQLite database with C#. The full exception message is “<code>Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.</code>” I originally built this database using access which worked fine, but for various reasons I had to recreate it using SQLite.</p> <p>To give a bit of background - this is a simple status scheduling program. Each <code>Status</code> has an associated <code>Account</code> and <code>Schedule</code>. I realise <code>Statuses</code> and <code>Schedule</code> is a 1:1 relationship and could be in the same table but to allow the program to develop further I have split them into two tables.</p> <p>See below for a cut down version of my table script (this is enough to recreate the problem).</p> <pre><code>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); </code></pre> <p>I did not have any issues until I created two <code>Statues</code> and associated them to the same <code>Account</code>. </p> <pre><code>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 </code></pre> <p>The select statement I’m using which throws the exception is:</p> <pre><code>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 </code></pre> <p>If I run the same query, but remove the ‘<code>Accounts.Id</code>’ column the query works fine.</p> <p>See below for the C# code I’m using but I don’t think this is the problem</p> <pre><code>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; } </code></pre> <p>Any help will be appreciated. Thanks.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload