Composite primary keys in SQLite.Net

Composite primary keys in SQLite.Net

SQLite.Net-PCL is one of the best SQLite client/ORM out there in a PCL form supporting Windows Store and Universal apps. It's open source, but it's not feature complete, and you may learn the hard way that some things are missing. Being open source is a good thing, which means that you can add support for anything you can imagine or need yourself. One of the things missing (currently, in the master branch of the original repo) is support for composite primary keys.

Composite primary keys (or compound, there's a subtle difference) basically mean that you can use two or more attributes (columns) to uniquely define an entity in a table. It's not so difficult to imagine such a scenario, especially in junction tables. Just think of an university enrolment table that keeps track of courses and students enroled in those courses.

Exceptions, exceptions everywhere

Well, OK, not really everywhere - just one exception occurs when you try to create a table with composite primary key using SQLite.Net-PCL library - SQLiteException. :)

Imagine you have an oversimplified class called Enrolment which holds courses, students enroled in those courses and date of enrolment.

[Table("Enrolment")]
public class Enrolment  
{
    [PrimaryKey]
    public int StudentId { get; set; }

    [PrimaryKey]
    public int CourseId { get; set; }

    public DateTime DateOfEnrolment { get; set; }
}

If you try to create this table:

using (var conn = new SQLiteConnection(new SQLite.Net.Platform.Generic.SQLitePlatformGeneric(), dbPath))  
{
    conn.CreateTable<Enrolment>();
}

you'll get an exception that the table has more than one primary key:

SQLiteException - table has more than one primary key

Oh no! Now what?

Support for composite primary keys

As I added the support for composite primary keys, I thought that the best thing to do would be to get it back into the main repository and master branch, so I opened a pull request.

https://github.com/oysteinkrog/SQLite.Net-PCL/pull/160

It might take a while before it gets merged in because it's important not to break the library for others, so I'll probably have to write a few more tests. In the meantime, I wanted to let everyone know that the pull request was there. So let's discuss the changes I made.

The first thing is what happens during table creation. Normally, with one primary key, the syntax goes like this:

CREATE TABLE t1(a, b PRIMARY KEY);  

If primary key spans over multiple columns, you'd do something like this:

CREATE TABLE t1(a, b, c, PRIMARY KEY (a, b));  

So an important change here was to move the primary key definition to the end, after all the columns have been listed

if (PKs.Count > 1)  
{
    query.Append("primary key (").Append(string.Join(",", PKs.Select(pk => pk.Name))).Append(")");
}

At first I thought about changing the PK property of TableMapping and call it PKs, making it an array of columns instead of just one. But as it's a breaking change for everyone using the library at the moment, there are now to properties, PK and PKs, where getting PK throws an exception if the table has composite primary key.

[PublicAPI]
public Column PK  
{
    get
    {
        if (HasCompositePK)
        {
            throw new NotSupportedException("Table has a composite primary key. Use PKs property instead.");
        }
        else
        {
            return _pk;
        }
    }
}

[PublicAPI]
public Column[] PKs { get; private set; }  

Yeah, I know that calling it PKs sounds like there are more primary keys, even though technically there's one, spanning over multiple columns. Calling it CompositePK would certainly be more descriptive and better. I'm thinking of changing it.

The queries for getting, finding and deleting entities based on primary keys needed to be changed, too, if table has a composite PK.

Instead of

DELETE FROM Enrolment WHERE StudentId=1;  

it needs to do

DELETE FROM Enrolment WHERE StudentId=1 AND CourseId=5;  

and so on...

I made it so that the primary keys need to be passed in as a dictionary of string/object pairs where key is the name of the column in composite PK, and object is the value.

IDictionary<string, object> PKs = pk as Dictionary<string, object>;  

Then I verify that the column names match and use the values to make the query.

if (PKs.Keys.Intersect(pks.Select(p => p.Name)).Count() < pks.Length)  
{
    throw new NotSupportedException("Cannot find in " + map.TableName + ": PKs mismatch. Make sure PK names are valid.");
}
return Query(map, map.GetByPrimaryKeySql, PKs.Values.ToArray()).FirstOrDefault();  

What's left to do?

Some code cleanup would be nice. And of course - more tests. There may be some cases where certain properties such as autoincrement won't work if there's a composite PK. I'm hoping to get more tests in soon (maybe some more feedback, too), so it can be merged in and everyone can start using it. Without breaking anything. :)

I tried to use the same code style as the author did even though I'd normally write some things differently. Also, thanks to Shiney for some really good ideas and comments on my pull request!

So, if I go back and try to create the Enrolment table I showed you earlier, it creates it without exceptions and problems.

SQLite table with composite primary key

If you have any suggestions and comments, leave a comment!

https://github.com/oysteinkrog/SQLite.Net-PCL/pull/160

Igor Ralic

igor ralic

View Comments
Microsoft Certified Solutions Developer: Windows Store Apps in C#