Unique field combination in SQLite?

Question

I'm trying to populate a new SQLite database with rows based on a dataset, but I'm having trouble deleting duplicate rows. I could do this in Python, but in SQLite, there certainly should be a design option to handle.

I need each line to exist only for a unique combination of three text fields. If I make each text field bounded using UNIQUE, then all three must be unique. But instead, I would choose a unique combination of three lines.

In other words, these records should be able to exist: (a, a, a) (a, a, b) (a, b, b) (b, b, b)

If I do all three UNIQUE fields and insert these rows, only (a, a, a) and (b, b, b) are inserted. I could concatenate fields 1-3 in Python and use this as a primary key, but this seems like extra work.

Answer 1

CREATE TABLE (col1 typ , col2 typ , col3 typ , CONSTRAINT unq UNIQUE (col1, col2, col3)) 

http://www.sqlite.org/lang_createtable.html

Answer 2

If the three columns are indeed the primary key, you can create a composite primary key:

 create table t ( a text not null, b text not null, c text not null, -- and whatever other columns you have... primary key (a, b, c) ) 

If any of your three columns may be NULL, then you want a unique Cade constraint.