System.Data.SQLite

Any possibility of having a System.Data.SQLite that uses the newer version of SQLite this year? I have held off using SQLite and this provider since I really want foreign key support. I'm not complaining or trying to rush you, just wondering if you could give any indication?

 

 

I really need to get going on the foriegn key support so I put the 3 new 3.6.20 SQLite source files into the SQLite.Interop project. Rebuilt, installed System.Data.SQLite using the existing installer, then copied my new versions of the dlls to the bin folder. Seems to work, and I now seem to have foreign keys working - an error occurs if a constraint is violated. All the System.Data.SQLite project tests pass. The only change to get it to compile is at about line 444 of interop.c. The VdbeCursor structure changed, and I have no idea what I'm breaking. I will of course use the official version when it's ready.

   //else if(pC->pseudoTable)
    //{
    //  *prowid = pC->iKey;
    //}
 

 I also added a 'Foreign Keys' property to ConnectionString in SQLiteConnection class.

Foreign Keys=ON
Foreign Keys=OFF

 which issues

 PRAGMA foreign_keys=ON

 PRAGMA foreign_keys=OFF

 This sounds really good. Does Foreign Key support mean that triggers are no longer needed for enforcing RI?

Can you post the updated bits somewhere along with a sample showing how FKs are enforced?

 

This sounds really, really good!

 Yes, foreign key triggers don't need to be explicity written anymore. I think they are written internally on the fly inside sqlite engine now. RI is enforced, almost annoyingly so.

My code is working, but his build is somewhat hard to learn without docs, and the install is a separate project. System.Data.SQLite includes sqlite engine using a linker trick. The system.data.sqlite .net project is linked into a win32 dll which is output as System.Data.SQLite.dll. I didn't know you could do that. 

I could put my code someplace, let me think about that. There aren't that many changes as far as I know, and I have not dealt with the designer at all and don't know what changes he will make there. I wish this project was on github and we could all pound on the code and submit changes.

The new foreign key feature is documented pretty accurately on the main sqlite site. You have to be careful that foreign keys must match the primary key of the parent table exactly or else you get a 'foreign key mismatch' error. I have found that cascading deletes are working automatically now if you use "ON DELETE CASCADE".

 

 

Hi, 

Is this new version of SQLite.dll released? I added the FOREIGN KEYS=ON; to the connection string. As well as all my foreign key constraints have the ON DELETE CASCADE clause. However, deleting a parent record does not delete the child records. I am not able to get it to work unless I use triggers.

The current version of SQLite.dll I am using is 1.0.65.0

I don't think it's released yet? Would be cool it was released with a mini tutorial as well

I think I have confused you. My posts refer to my own home grown version.

We must all wait until the official version is out, so keep watching the home page. The connection string setting for foreign keys will be different from mine.

 

 Could you point out what files you modified and where in order to work?

 

Thank you.

 You can now download the latest version 1.0.66.0. You can do a diff with the source for 1.0.65.0 to see what changed.

Are you saying that foreign key constraints should work in 1.0.66.0 because they don't for me? I tried adding "PRAGMA foreign_keys = ON;" to the front of my insert command but it still lets me insert rows with foreign keys that don't exist in the referenced table. Is there anyway I have have foreign key constraints enforced using System.Data.SQLite without changing and compiling it myself?

zeroid:

Are you saying that foreign key constraints should work in 1.0.66.0 because they don't for me? I tried adding "PRAGMA foreign_keys = ON;" to the front of my insert command but it still lets me insert rows with foreign keys that don't exist in the referenced table. Is there anyway I have have foreign key constraints enforced using System.Data.SQLite without changing and compiling it myself?

Does your foreign key column have a NOT NULL constraint? By default, SQLite will allow NULLs in foreign key columns.

       -Steve

Thanks for the information, very useful, if you have more information please tell us, thanks for sharing.

I tested this out using a simple SQLite database with two tables using Entity Framework 4.

The entity objects themselves do not enforce primary key constraints. Not sure if this is a bug; I know that for LINQ 2 Entities for SQL Server, the entities do enforce the PK restraint (before it hits the DB).

Note that SQLite by default does not enforce foreign keys. More info on how SQLite enforces foreign keys: http://www.sqlite.org/foreignkeys.html. When sending the PRAGMA command, you have to be sure that it's in the context of a long-lived connection. EF does not do this by default; see http://msdn.microsoft.com/en-us/library/bb896325(v=VS.100).aspx.

I've got it working (correctly rejecting an EF request to store a child record pointing to a nonexistent parent record) with this simple test code:

    using (var db = new testEntities())
     {
         db.Connection.Open();
         db.ExecuteStoreCommand("PRAGMA foreign_keys = ON;");
         var attorney = new Attorney { Name = "Bob", Bank = 1 };
         db.Attorneys.AddObject(attorney);
         db.SaveChanges(); // throws UpdateException wrapping a SQLiteException: "Abort due to constraint violation   foreign key constraint failed"
     }

       -Steve

 Hello, All

i wrote a little note about foregin key . But i dont know how to work it. Please suggest me.

In the context of relational databases, a foreign key is a referential constraint between two tables. The foreign key identifies a column or a set of columns in one (referencing) table that refers to set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don't exist in the referenced table. This way references can be made to link information together and it is an essential part of database normalization. Multiple rows in the referencing table may refer to the same row in the referenced table.

 

Regards,

I like to read a lot. share market tips Play a crucial role in today’s economy; they are the key contributors towards a country’s GDP growth. India in the last decade has established itself as a powerful economy and it is growing at a healthy rate. One of the key constituents of India’s robust economy is its stock market.