Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Unique combination of fields in SQLite?

I'm trying to populate a new SQLite database with rows based on a set of data, but I'm having trouble with avoiding duplicate rows. I could accomplish this in Python, but there certainly must be a design option in SQLite to handle this.

I need each row to exist for only a unique combination of three text fields. If I make each text field constrained with UNIQUE, then all three must be unique. But I would instead like a unique combination of the three strings.

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

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

like image 589
Thomas Avatar asked Sep 13 '11 19:09

Thomas


2 Answers

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

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

like image 113
Cade Roux Avatar answered Nov 05 '22 09:11

Cade Roux


If the three columns really are the primary key then you can make 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 can be NULL then you'd want to get with Cade's unique constraint instead.

like image 34
mu is too short Avatar answered Nov 05 '22 09:11

mu is too short