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…
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.
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.
The Public Void 