Skip to content

Multi-Column Unique Constraints in SQLite.NET

October 17, 2010

While working on my blog engine project to learn ASP.NET MVC and Entity Framework, I encountered a common scenario.  I’m using SQLite on the back-end, and I needed a unique constraint across two columns.  As demonstrated in this stackoverflow question, you can specify a unique table constraint immediately after the column definitions when using the CREATE TABLE command.  However, for the purpose of rapid prototyping, I prefer using SQLite.NET and the graphical Server Explorer interface in Visual Studio rather than explicit DDL to define my tables.

The problem is that the GUI-based designer is still in development and doesn’t support unique table constraints.  One solution is to create a unique index instead.  Right-click one of the row headers in the table designer and select Indexes/Keys…

Table design context menu

In the Index Editor, add a new index.  Under the Misc category, set the Unique property to True, and under the Source category, list the columns you want in the index, separated by commas.

Index editor

This works because, as stated in the SQLite documentation:

INTEGER PRIMARY KEY columns aside, both UNIQUE and PRIMARY KEY constraints are implemented by creating an index in the database (in the same way as a “CREATE UNIQUE INDEX” statement would).

Using the Index Editor to create an index with Unique = True generates a “CREATE UNIQUE INDEX” statement behind the scenes, which can be verified by using the Generate Change Script… feature.

In summary, if you’re thinking about using Server Explorer to create a multi-column unique constraint in SQLite, just go with a unique index, since that’s how it’s implemented anyway.

Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: