Option to make composite UNIQUE constraint on tables

Feature Request
1

I want to define a composite unique constraint in a multi-reference table in Database Manager.

This is typically accomplished with:
UNIQUE ("UserId", "TaskId")

For example, the table below should only allow one record for each user and task. Multiple tasks per user can be allowed, but the user (e.g. user 1) and task (e.g. task 1) should never be duplicated.

Structure

image

Example data

UserId TaskId IsPinned IsStarred
user1 task1 TRUE FALSE
user1 task2 FALSE TRUE
user2 task1 TRUE TRUE

With the UNIQUE ("UserId", "TaskId") constraint:

  • Allowed: Inserting a new record with user2 and task2 because this combination doesn't exist yet.
  • Not Allowed: Inserting a duplicate record with user1 and task1 because this combination already exists.
2

Reworded title to make it easier to read, I almost skipped it :laughing:

Can change it back if you disagree

For the record, I was planning to use a composite UNIQUE for non-reference columns, that's why I removed those words from the title, so it looks more generic and not specifically tied to reference columns
e.g.
UNIQUE ("slug", "version")
There can only be one blog post with slug XYZ per version (ignore if it doesn't make sense, I just came up with this example)

2 Likes
3

Needing this right now as well.. Would simplify upserts for me a lot

4

Bump