4

I want to create a database for my program. Most likely I will use SQLite, although others are an option.

I will have columns a, b, c, d. a is unique and will be my primary key. b, c, d are allowed to contain duplicate value.

However, I don't want combinations of b, c, d to repeat. For instance, sample valid db:

a   b   c   d
0   18  n   kiwi
1   12  v   kiwi
2   15  t   orange
3   12  j   kiwi
4   12  t   banana
5   31  f   apple
6   10  h   kiwi
7   15  y   orange
8   20  b   apple
9   1   m   grapefruit
10  7   s   apple
11  4   h   banana
12  31  t   egg

Sample invalid db (rows 2 and 7):

a   b   c   d
0   18  n   kiwi
1   12  v   kiwi
2   15  y   orange
3   12  j   kiwi
4   12  t   banana
5   31  f   apple
6   10  h   kiwi
7   15  y   orange
8   20  b   apple
9   1   m   grapefruit
10  7   s   apple
11  4   h   banana
12  31  t   egg

Is there standard database rule or concept that applies to this?

I can of course simply do validation in my code, by manually querying the db. I believe I could also construct a fifth bcd column, which concatenates the other three, and is required to be unique. But both of these seem hacky. Is it possible to somehow "declare the columns unique in combination only" in a more direct and obvious way?

For instance, I could declare (b, c, d) as my composite primary key (not sure if SQLite supports this but I think SQL does). That should accomplish my goal. However, I don't want these columns to actually be my PK.

  • 2
    You need a UNIQUE constraint/index on (b,c,d). In SQLite there's no difference between a PRIMARY KEY and a UNIQUE constraint/index on NOT NULL columns. (except that you can have many UNIQUE ones but only one PRIMARY KEY). See: sqlite.org/lang_createtable.html – ypercubeᵀᴹ Jan 20 '18 at 10:27
  • (and the exception about single-column integer primary keys that have some special properties). – ypercubeᵀᴹ Jan 20 '18 at 10:34
3
CREATE UNIQUE INDEX your_unique_index ON your_table(b, c, d);

Create an index instead. It has the same as the unique constraint.

  • 1
    Are you suggesting that the OP create a unique index instead of a unique constraint? If so, why? – Andriy M Jan 21 '18 at 10:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.