SQL / JPA "conditional" uniqueness a thing?

I have a OneToMany Relation Ship in my entities. There is a entity article an a entity draft. Every draft has a unique version in connected with an article.

aritcle_id | draft_id | version
-------------------------------
 1         | 1        | 1
 1         | 2        | 2
 1         | 3        | 3
 2         | 4        | 1

If I make the version 'unique' it is version-row-far unique is it possible to declare it article-row-far unique? So you can have duplicated versions but only if the version is attached to another article?

1 answer

  • answered 2020-07-14 19:14 Gordon Linoff

    Every draft has a unique version

    You can implement this with a unique constraint. I think it is:

    alter table t add constraint unq_t_draft_version unique (draft_id, version);
    

    If draft_id can be repeated on different articles, you can include all three columns:

    alter table t add constraint unq_t_article_draft_version unique (article_id, draft_id, version);