Is there a conditional unique constraint?

Phoenix Forum Questions / Help
1

Let’s say that there are Books and Authors.

  1. There can be many Books with the same title
  2. There can be many Authors with the same name
  3. However, in this application there cannot be multiple Books with the same title with Authors with the same name.

What would be the best way to do this?

2

Can you just clarify a bit.

  • An Author’s books must have unique names?
    (This can be solved with a unique index on the book title and author_id in the book table)

or

  • The Author name and Book name combination must be unique?
    So two different authors named Jane Smith may both not have a book called “Foobar”.
1 Like
3

Hi @Linuus ,

Thanks for asking those questions. They led me to finding my answer haha

Complex constraints

Because the constraint logic is in the database, we can leverage all the database functionality when defining them. For example, let’s suppose the e-mails are scoped by company id. We would write in a migration:

create unique_index(:users, [:email, :company_id])

4

How would you define the unique_constraint in the changeset?

# In the migration
create unique_index(:books, [:title, :author_id])

# in the changeset function
|> unique_constraint(:title, name: ???)

In the doc I see the following example. Does this mean that it’s standard practice to name our unique index?

# In the migration
create unique_index(:users, [:email, :company_id], name: :posts_email_company_id_index)

# In the changeset function
cast(user, params, [:email])
|> unique_constraint(:email, name: :posts_email_company_id_index)
5

Yes you should name the index when using a composite index (multiple columns). This is because you need to explicitly tell the unique_constraint function what index constraint it maps to.

Examples:

Single column index

# migration
create unique_index(:books, [:title]) # The index is named something like title_idx

# changeset
...
|> unique_constraint(:title) # don't need the name here

You don’t need the name in the unique_constraint function because it’s obvious that a failure on title_idx (or whatever the default naming is) maps to the title field.

Multi column index

# migration
create unique_index(:books, [:title, :author_id]) # The index is named something like title_author_id_idx

# changeset
...
|> unique_constraint(:title) # not clear if a failure on title_author_id_idx maps to this?!

In this example it’s not really clear if when that index fails should actually map to that field. So, the easiest thing is to just be explicit and name the index.

# In the migration
create unique_index(:books, [:title, :author_id], name: :title_author_id_index)

# in the changeset function
|> unique_constraint(:title, name: :title_author_id_index)

The name doesn’t really matter since you map it explicitly to your field. It could be called :foobar_index but it’s good to give it a good name :smiley:

2 Likes