sqlite - Unique combination of values - Database Administrators Stack Exchange

i want create database program. use sqlite, although others option.

i have columns a, b, c, d. a unique , primary key. b, c, d allowed contain duplicate value.

however, don't want combinations of b, c, d repeat. 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 , 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 applies this?

i can of course validation in code, manually querying db. believe construct fifth bcd column, concatenates other three, , required unique. both of these seem hacky. possible somehow "declare columns unique in combination only" in more direct , obvious way?

for instance, declare (b, c, d) composite primary key (not sure if sqlite supports think sql does). should accomplish goal. however, don't want these columns pk.

create unique index your_unique_index on your_table(b, c, d); 

create index instead. has same unique constraint.