How to use Ecto.Migration constraint to enforce only one field

Question

I have a table called Accounts that must have either a user or organization that "owns" it. I want to enforce a constraint that enforces that EITHER user_id or organization_id has been provided, but not both or none. The table looks like this:

  def change do
    create table(:accounts, primary_key: false) do
      add(:id, :binary_id, primary_key: true)
      add(:user_id, references(:users, type: :binary_id), null: true)
      add(:organization_id, references(:organizations, type: :binary_id), null: true)

      timestamps()
    end

I could easily do this in a changeset, but I'm interested in how I would handle this with a constraint using Ecto.Migration.constraint. I'm having a little trouble wrapping my head around how I use these and how I would implement what I'm trying to do with it. Any help is appreciated.

score 2 · Answer 1 · answered Apr 11 '19 at 06:51

  def change do
    # create table

    create constraint(:accounts, :user_or_organization, check: "((organization_id is not null and user_id is null) or (organization_id is null and user_id is not null))")
  end

Referencing solution from here this is how you can apply this with Ecto Migrations. Note that create table and constraint doesn't have to be in the same migration, you can add in the constraint later, but you can't have rows already in the table that violate that constraint.

How to use Ecto.Migration constraint to enforce only one field

1 Answers1