CODEWITHSUNDEEP
pythonsqlitedatabase-designunique-constraint
Thomas
Thomas

Reputation: 191

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.

Upvotes: 19

Views: 9843

Answers (2)

mu is too short
mu is too short

Reputation: 434735

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.

Upvotes: 4

Cade Roux
Cade Roux

Reputation: 89711

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

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

Upvotes: 31

Related Questions